1 Reply Latest reply: Jun 16, 2017 11:00 AM by Andrey Khoronenko

# Date Comparison in qliksense

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:

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

Acc,

Day,

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

Resident master01 Order by Acc,Day Desc;

How to find inactive account any suggestion please..

• ###### Re: Date Comparison in qliksense

Hi,

May be like this (look attached file)

Table1:

[Acc, Day

A, 1

B, 1

C, 1

D, 1

E, 1];

Table2:

[Acc, Day

A, 2

C, 2

F, 2

D, 2

M, 2

];

Table3:

[Acc, Day

B, 3

G, 3

H, 3

A, 3

J, 3

];

NoConcatenate

Table4:

Day,

Concat(Acc, '') as Acc

Resident Table1

Group By Day;

Drop Table Table1;

Left Join

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