From the above input table, i need to display the below rows only.
ID . FlagCheck . user
9878 . 0 . userB
4534 . 0 . userC
The logic that needs to be implemented is to check when the flag is clear(value=0) but the flag HAS to be uncleared in its previous level. Thats the reason UserA should not show up in this report.
Thanks Rakesh. that syntax did help. However, I cannot created that syntax as a dimension as it would give me a “multivalue error”. The sane way would be to created the formula as a measure.
Since it being a measure, I cannot apply any global filters, and not able to disregard the NULL records.
P.S : please would you be able to provide me this implementation in SQL format.
The advise from Rakesh did help, but i am stuck at displaying just those records. Attached is the report and after using the logic, i am able to show just the 30283 and 22156 records.
My main goal is to either display just these two records without the first two columns or a count of 2 without any other columns.
Can you please once again provide what is your input data and what output you are looking for. please provide in excel sheet or as a screenshot, as I am not able to open your report here.
The input is from two tables, joining by the ID and HISTID. The Orderid is of importance here as the changes of MIV from 1 to 0 are to be recorded for each Orderid.
Attached is the excel file with the input data and the preferred output. File1.xls (40.0 KB)
Unfortunately mak… its SQL server that I am using… if only microsoft would have allowed the lag and lead oracle functions to be accepted in sql server…
Rakesh,
That logic would/did not work as once the variable is created, it needs the presence of the used columns else it throws up a “computational error”.
An update… i was able to make some progress on the query part though… not entirely …
select distinct min(b.id) , b.orderid from (select d.id, e.orderid from table 1d, table2 e
where d.miv = 0
and d.histid = e.status_id
and d.id in (select id from table1
where histid in (select id from table2
where orderid in (‘64802’,‘64814’))
)
) b,
(select d.id, e.orderid from table 1d, table2 e
where d.miv = 1
and d.histid = e.status_id
and d.id in (select id from table1
where histid in (select id from table2
where orderid in (‘64802’,‘64814’))
)
) c
where b.orderid = c.orderid
and c.id < b.id
group by b.orderid
By using the above Freehand sql, i was able to restrict the data but only 22156 from Table1 shows up and now 30283 from Table1.
Rakesh,
Please could you post the report. I dont understand what am I doing wrong. I follow your solution step by step, but it doesn’t work. I get a computational error if I were to remove the “MIV” column, and I am not able to apply the ranking as, the variable created which is a measure does not show up in the “based on” drop down during ranking.
That exactly what i did. Step-by-step… nothing more…
i just could not figure out what the problem is/was…
however, i did find a solution but i had to develop it using a SQL… i had to use the rownum functionality to capture the previous row value, which worked and solved the problem.
(BOB member since 2007-12-11)
(BOB member since 2005-01-06)
(BOB member since 2007-12-11)