Solved: Count Days between field value - Qlik Community

ch_riadh · ‎2020-08-11

Hi.

I have Date for Account Status.

Date, Sequence, Status ( Exceed , Within Limit)

DATE	SEQUENCE	BALANCE	STATUS
30/09/2018	1,536	-21555	Exceed
30/09/2018	1,537	-24930	Exceed
26/10/2018	1,538	-25190	Exceed
28/11/2018	1,539	1000310	Within Limit
28/11/2018	1,540	310	Within Limit
09/12/2018	1,541	244	Within Limit
17/12/2018	1,542	-272	Exceed
31/12/2018	1,543	-13074	Exceed
31/12/2018	1,544	-13154	Exceed
02/01/2019	1,545	-13177	Exceed
21/01/2019	1,546	986823	Within Limit
21/01/2019	1,547	883787	Within Limit
25/01/2019	1,548	883528	Within Limit
27/01/2019	1,549	-116472	Exceed
27/01/2019	1,550	-121650	Exceed
31/01/2019	1,551	-627487	Exceed
04/02/2019	1,552	-1357780	Exceed
05/02/2019	1,553	-1358615	Exceed
18/02/2019	1,554	-1358613	Exceed
20/02/2019	1,555	-358613	Exceed
20/02/2019	1,556	321387	Within Limit
21/02/2019	1,557	321072	Within Limit
03/03/2019	1,559	-684873	Exceed

I want to count :

How many days the Account keep same status.

For Exemple :

From 30/09/2018 to 27/11/2018 : Exceed : 59 days

From 28/11/2018 to 16/12/2018 : within limit : 48 days

From 17/12/2018 to 20/01/2019 : Exceed : 35 days

From 21/01/2019 to 26/01/2019 : within limit : 6 Days

etc.

And Create Line Chart for That

Note : if i have to sequence in same day, we should take the max(sequence) only

Thank you

Output should be like this :

DATE	STATUS
30/09/2018	Exceed
01/10/2018	Exceed
02/10/2018	Exceed
03/10/2018	Exceed
04/10/2018	Exceed
05/10/2018	Exceed
06/10/2018	Exceed
07/10/2018	Exceed
08/10/2018	Exceed
09/10/2018	Exceed
10/10/2018	Exceed
11/10/2018	Exceed
12/10/2018	Exceed
13/10/2018	Exceed
14/10/2018	Exceed
15/10/2018	Exceed
16/10/2018	Exceed
17/10/2018	Exceed
18/10/2018	Exceed
19/10/2018	Exceed
20/10/2018	Exceed
21/10/2018	Exceed
22/10/2018	Exceed
23/10/2018	Exceed
24/10/2018	Exceed
25/10/2018	Exceed
26/10/2018	Exceed
27/10/2018	Exceed
28/10/2018	Exceed
29/10/2018	Exceed
30/10/2018	Exceed
31/10/2018	Exceed
01/11/2018	Exceed
02/11/2018	Exceed
03/11/2018	Exceed
04/11/2018	Exceed
05/11/2018	Exceed
06/11/2018	Exceed
07/11/2018	Exceed
08/11/2018	Exceed
09/11/2018	Exceed
10/11/2018	Exceed
11/11/2018	Exceed
12/11/2018	Exceed
13/11/2018	Exceed
14/11/2018	Exceed
15/11/2018	Exceed
16/11/2018	Exceed
17/11/2018	Exceed
18/11/2018	Exceed
19/11/2018	Exceed
20/11/2018	Exceed
21/11/2018	Exceed
22/11/2018	Exceed
23/11/2018	Exceed
24/11/2018	Exceed
25/11/2018	Exceed
26/11/2018	Exceed
27/11/2018	Exceed
28/11/2018	Within Limit
29/11/2018	Within Limit
30/11/2018	Within Limit
01/12/2018	Within Limit
02/12/2018	Within Limit
03/12/2018	Within Limit
04/12/2018	Within Limit
05/12/2018	Within Limit
06/12/2018	Within Limit
07/12/2018	Within Limit
08/12/2018	Within Limit
09/12/2018	Within Limit
10/12/2018	Within Limit
11/12/2018	Within Limit
12/12/2018	Within Limit
13/12/2018	Within Limit
14/12/2018	Within Limit
15/12/2018	Within Limit
16/12/2018	Within Limit
17/12/2018	Exceed
18/12/2018	Exceed
19/12/2018	Exceed
20/12/2018	Exceed
21/12/2018	Exceed
22/12/2018	Exceed
23/12/2018	Exceed
24/12/2018	Exceed
25/12/2018	Exceed
26/12/2018	Exceed
27/12/2018	Exceed
28/12/2018	Exceed
29/12/2018	Exceed
30/12/2018	Exceed
31/12/2018	Exceed
01/01/2019	Exceed
02/01/2019	Exceed
03/01/2019	Exceed
04/01/2019	Exceed
05/01/2019	Exceed
06/01/2019	Exceed
07/01/2019	Exceed
08/01/2019	Exceed
09/01/2019	Exceed
10/01/2019	Exceed
11/01/2019	Exceed
12/01/2019	Exceed
13/01/2019	Exceed
14/01/2019	Exceed
15/01/2019	Exceed
16/01/2019	Exceed
17/01/2019	Exceed
18/01/2019	Exceed
19/01/2019	Exceed
20/01/2019	Exceed
21/01/2019	Within Limit
22/01/2019	Within Limit
23/01/2019	Within Limit
24/01/2019	Within Limit
25/01/2019	Within Limit
26/01/2019	Within Limit
27/01/2019	Exceed
28/01/2019	Exceed
29/01/2019	Exceed
30/01/2019	Exceed
31/01/2019	Exceed
01/02/2019	Exceed
02/02/2019	Exceed
03/02/2019	Exceed
04/02/2019	Exceed
05/02/2019	Exceed
06/02/2019	Exceed
07/02/2019	Exceed
08/02/2019	Exceed
09/02/2019	Exceed
10/02/2019	Exceed
11/02/2019	Exceed
12/02/2019	Exceed
13/02/2019	Exceed
14/02/2019	Exceed
15/02/2019	Exceed
16/02/2019	Exceed
17/02/2019	Exceed
18/02/2019	Exceed
19/02/2019	Exceed
20/02/2019	Within Limit
21/02/2019	Within Limit
22/02/2019	Within Limit
23/02/2019	Within Limit
24/02/2019	Within Limit
25/02/2019	Within Limit
26/02/2019	Within Limit
27/02/2019	Within Limit
28/02/2019	Within Limit
01/03/2019	Within Limit
02/03/2019	Within Limit
03/03/2019	Within Limit

Thank you

Kushal_Chawda · ‎2020-08-13

Data:
LOAD TRS_NO, 
     SEQUENCE, 
     TRANS_DATE, 
     TRANS_AMOUNT, 
     BALANCE, 
     AccountNo, 
     TYPE
FROM
[C:\TEST.xlsx]
(ooxml, embedded labels, table is DATA);

T1:
LOAD *,
     if(AccountNo=Peek(AccountNo), date(Peek(TRANS_DATE)-1),TRANS_DATE) as Max_Trans_Date
Resident Data
Order by AccountNo,TRANS_DATE desc;

Drop Table Data;

Join(T1)
LOAD AccountNo,
     TRANS_DATE+IterNo()-1 as TRANS_DATE
Resident T1
While TRANS_DATE+IterNo()-1<=Max_Trans_Date;

T2:
NoConcatenate
load TRS_NO, 
     if(isnull(SEQUENCE),Peek(SEQUENCE),SEQUENCE) as SEQUENCE,
     if(isnull(BALANCE),Peek(BALANCE),BALANCE) as BALANCE,
     if(isnull(TYPE),Peek(TYPE),TYPE) as TYPE,
     TRANS_DATE, 
     TRANS_AMOUNT, 
     AccountNo
Resident T1
Order by AccountNo,TRANS_DATE;

DROP Table T1;

View solution in original post

sunny_talwar · ‎2020-08-11

Have you looked into doing an Intervalmatch

Kushal_Chawda · ‎2020-08-11

Data:
LOAD
    "DATE",
    SEQUENCE,
    BALANCE,
    STATUS
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);

Inner Join(Data)
Load "DATE",
      max(SEQUENCE) as SEQUENCE,
      STATUS
Resident Data
Group by "DATE",STATUS;

T1:
Load *,
     if(STATUS<>Peek(STATUS),alt(rangesum(Peek(Group),1),1),Peek(Group)) as Group
Resident Data
Order by "DATE",SEQUENCE desc;

Drop Table Data;

T2:
Load Group,
     date(min("DATE")) as MinDate
Resident T1
Group by Group;

Left Join(T1)
Load Group,
     MinDate,
     date(alt(Peek(MinDate),MinDate+1)-1) as MaxDate
Resident T2
Order by MinDate desc;

Drop Table T2;

Join(T1)
Load 
     Group,
     date(MinDate+IterNo()-1) as DATE
resident T1
While MinDate+IterNo()-1<=MaxDate;

Drop Fields MinDate,MaxDate;

Final:
NoConcatenate
Load DATE,
    if(IsNull(SEQUENCE),Peek(SEQUENCE),SEQUENCE) as SEQUENCE,
    if(IsNull(BALANCE),Peek(BALANCE),BALANCE) as BALANCE,
    if(IsNull(STATUS),Peek(STATUS),STATUS) as STATUS,
    Group
Resident T1
Order by Group,DATE;

Drop Table T1;

ch_riadh · ‎2020-08-12

Thank you, Just to clarify.

I have a transaction table with 8 fields:

TRS_NO	SEQUENCE	TRANS_DATE	TRANS_AMOUNT	BALANCE	AccountNo	TYPE
101	10011	1/13/2020	1000	1000	1001	ABC
102	10021	1/13/2020	2000	2000	1002	XYZ
103	10022	1/15/2020	900	1100	1002	XYZ
104	10012	1/17/2020	400	1400	1001	ABC
105	10023	1/17/2020	1100	0	1002	XYZ
106	10013	1/17/2020	-1600	-200	1001	ABC
107	10024	1/19/2020	200	-200	1002	XYZ
108	10014	1/20/2020	200	0	1001	ABC

I want to fill data for the non transaction date like the table below.

This is the output i want :

TRS_NO	SEQUENCE	SEQUENCE2	TRANS_DATE	TRANS_AMOUNT	BALANCE	BALANCE2	AccountNo	TYPE
101	10011	10011	1/13/2020	1000	1000	1000	1001	ABC
102	10021	10021	1/13/2020	2000	2000	2000	1002	XYZ
NULL	NULL	10011	1/14/2020	NULL	NULL	1000	1001	ABC
NULL	NULL	10021	1/14/2020	NULL	NULL	2000	1002	XYZ
NULL	NULL	10011	1/15/2020	NULL	NULL	1000	1001	ABC
103	10022	10022	1/15/2020	900	1100	1100	1002	XYZ
NULL	NULL	10011	1/16/2020	NULL	NULL	1000	1001	ABC
NULL	NULL	10022	1/16/2020	NULL	NULL	1100	1002	XYZ
102	10012	10012	1/17/2020	400	1400	1400	1001	ABC
103	10013	10013	1/17/2020	-1600	-200	-200	1001	ABC
105	10023	10023	1/17/2020	1100	0	0	1002	XYZ
NULL	NULL	10013	1/18/2020	NULL	NULL	-200	1001	ABC
NULL	NULL	10023	1/18/2020	NULL	NULL	0	1002	XYZ
NULL	NULL	10013	1/19/2020	NULL	NULL	-200	1001	ABC
107	10024	10024	1/19/2020	200	-200	-200	1002	XYZ
104	10014	10014	1/20/2020	200	0	0	1001	ABC

Please find attached Excel with the 2 tables.

With this script it works for one accountno !!

//----------------------

Data:
LOAD

TRANS_AMOUNT,
SEQUENCE,
TRANS_DATE,
BALANCE,
AccountNo

FROM [lib://DATA/HIST.qvd]
(qvd);

T1:
Load *,

if(BALANCE<>Peek(BALANCE) AND SEQUENCE<>Peek(SEQUENCE)
, alt(rangesum(Peek(Group),1),1),Peek(Group)) as Group
Resident Data
Order by TRANS_DATE,SEQUENCE desc;

Drop Table Data;

T2:
Load Group,
date(min(TRANS_DATE)) as MinDate
Resident T1
Group by Group;

Left Join(T1)
Load Group,
MinDate,
date(alt(Peek(MinDate),MinDate+1)-1) as MaxDate
Resident T2
Order by MinDate desc;

Drop Table T2;

Join(T1)
Load
Group,
date(MinDate+IterNo()-1) as TRANS_DATE
resident T1
While MinDate+IterNo()-1<=MaxDate;

Drop Fields MinDate,MaxDate;

Final:
NoConcatenate
Load
TRANS_DATE,
TRANS_AMOUNT,
AccountNo,
SEQUENCE,
BALANCE,

if(IsNull(SEQUENCE),Peek(SEQUENCE2),SEQUENCE) as SEQUENCE2,
if(IsNull(BALANCE),Peek(BALANCE2),BALANCE) as BALANCE2,
if(IsNull(AccountNo),Peek(AccountNo2),AccountNo) as AccountNo2,

Group
Resident T1
Order by Group,TRANS_DATE,SEQUENCE, AccountNo;

Drop Table T1;

//--------------------------------

Thank you

Kushal_Chawda · ‎2020-08-12

This looks different than what you had asked earlier. Let me look at it

ch_riadh · ‎2020-08-13

Can i have your contact, maybe i can explain better.

Thank you

Kushal_Chawda · ‎2020-08-13

Data:
LOAD TRS_NO, 
     SEQUENCE, 
     TRANS_DATE, 
     TRANS_AMOUNT, 
     BALANCE, 
     AccountNo, 
     TYPE
FROM
[C:\TEST.xlsx]
(ooxml, embedded labels, table is DATA);

T1:
LOAD *,
     if(AccountNo=Peek(AccountNo), date(Peek(TRANS_DATE)-1),TRANS_DATE) as Max_Trans_Date
Resident Data
Order by AccountNo,TRANS_DATE desc;

Drop Table Data;

Join(T1)
LOAD AccountNo,
     TRANS_DATE+IterNo()-1 as TRANS_DATE
Resident T1
While TRANS_DATE+IterNo()-1<=Max_Trans_Date;

T2:
NoConcatenate
load TRS_NO, 
     if(isnull(SEQUENCE),Peek(SEQUENCE),SEQUENCE) as SEQUENCE,
     if(isnull(BALANCE),Peek(BALANCE),BALANCE) as BALANCE,
     if(isnull(TYPE),Peek(TYPE),TYPE) as TYPE,
     TRANS_DATE, 
     TRANS_AMOUNT, 
     AccountNo
Resident T1
Order by AccountNo,TRANS_DATE;

DROP Table T1;