Hi Team, I have a flat file and I need to create some aggregate fields from it using Script

[#1]

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

[sunny_talwar]

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;