Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Below is raw file,
Id | Date | Status |
1 | 1/5/2017 | Open |
1 | 1/6/2017 | Open |
1 | 1/8/2017 | Open |
1 | 1/9/2017 | Closed |
1 | 1/10/2017 | Open |
1 | 1/11/2017 | Open |
1 | 1/12/2017 | Open |
1 | 1/14/2017 | Cosed |
1 | 1/15/2017 | Open |
1 | 1/16/2017 | Open |
1 | 1/17/2017 | Open |
1 | 1/20/2017 | Open |
I need to create aggregate table that has some new columns and one column with Counter
I need Result like
Id | Open Date | Closed Date | Status | #Cases |
1 | 1/5/2017 | 1/9/2017 | Still Open | 1st Time |
1 | 1/10/2017 | 1/14/2017 | Still Open | 2nd Time |
1 | 1/15/2017 | Still Open | 3rd Time |
SO this table is actually telling us that Id was 1st Closed on 9th Jan then it again opened on 10th Jan and then 2nd time it closed on 14th Jan but then on 15th it again reopened, As this Id is still open hence we have Status still open,and I also have counter variable($ CAses) that says for how many time this Id opened
I need these variable using Qlikview script.
Thanks in Advance
Regards
Sid
Try this:
Table:
LOAD * INLINE [
Id, Date, Status
1, 1/5/2017, Open
1, 1/6/2017, Open
1, 1/8/2017, Open
1, 1/9/2017, Closed
1, 1/10/2017, Open
1, 1/11/2017, Open
1, 1/12/2017, Open
1, 1/14/2017, Closed
1, 1/15/2017, Open
1, 1/16/2017, Open
1, 1/17/2017, Open
1, 1/20/2017, Open
];
Table1:
LOAD *,
If(Id = Previous(Id),
If(Status = Previous(Status), Peek('Order'), RangeSum(Peek('Order'), 1)), 1) as Order
Resident Table
Order By Id, Date;
Table2:
NoConcatenate
LOAD Id,
Date(Min(Date)) as Date,
Status,
Order
Resident Table1
Group By Id, Status, Order;
Left Join (Table2)
LOAD Id,
FirstSortedValue(Status, -Date) as [Last Status]
Resident Table2
Group By Id;
FinalTable:
LOAD Id,
Date as [Open Date],
If([Last Status] = 'Open', 'Still Open', 'Closed') as Status,
RowNo() & ' Time' as [#Cases]
Resident Table2
Where Status = 'Open';
Left Join (FinalTable)
LOAD Id,
Date as [Closed Date],
If([Last Status] = 'Open', 'Still Open', 'Closed') as Status,
RowNo() & ' Time' as [#Cases]
Resident Table2
Where Status = 'Closed';
DROP Table Table, Table1, Table2;