Date Comparison in qliksense

[sankarraja]

Hi Folks,

i have a table like below

Table1:              Table2:                    Table3:

  Acc Day                Acc Day              Acc Day

    A    1                    A    2                    B    3

    B    1                    C    2                    G    3

    C    1                    F    2                    H    3

    D    1                    D    2                    A    3

    E    1                    M    2                    J    3

i have to show active and Inactive account based on day .

Active Acc is which is not repeated from the previous day

(ex:Day-1 have 5 active Acc because that was the first day & Day-2 have  two active Acc (F,M) compared with previous day  & Day-3 have four active Acc (B,G,H,J) compared with previous day.

Inactive Acc is which acc is not repeated from previous day Acc into the present account table

(ex: Day-3 have four inactive accounts(C,F,D,M) and Day-2 have two inactive account(B,E) and Day-1 have 0 inactive)

Output:

        Day                    1          2              3

active-Inactive          5-0        2-2          4-4

So

i using the below code for active account:

master:

Load

Acc,

Day,

if(Acc<>Previous(Acc),'Active') as flag

Resident master01 Order by Acc,Day;

Its working and got active accounts & am using same code with Descending order for inactive but its not working

Load

Acc,

Day,

if(Acc<>Previous(Acc),'Inactive') as flag

Resident master01 Order by Acc,Day Desc;

How to find inactive account any suggestion please..

[ahaahaaha]

Hi,

May be like this (look attached file)

Table1:

Load*Inline           

[Acc, Day           

A, 1                 

B, 1                   

C, 1                 

D, 1                 

E, 1];               

Table2:

Load*Inline

[Acc, Day

A, 2

C, 2

F, 2

D, 2

M, 2

];

Table3:

Load*Inline

[Acc, Day

B, 3

G, 3

H, 3

A, 3

J, 3

];

NoConcatenate

Table4:

Load

Day,

Concat(Acc, '') as Acc

Resident Table1

Group By Day;

Drop Table Table1;

Left Join

Load*,

If(RecNo()=1, Len(Acc), Len(Acc)-Len(KeepChar(Acc, Previous(Acc))))&'-'&

If(RecNo()=1, 0, Len(PurgeChar(Acc, Previous(Acc)))) as Active_InActive

Resident Table4;

Drop Field Acc;

Result

Regards,

Andrey