Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ch_riadh
Partner
Partner

Count Days between field value

Hi.

I have Date for Account Status.

Date, Sequence, Status ( Exceed , Within Limit)

DATESEQUENCEBALANCESTATUS
30/09/20181,536-21555Exceed
30/09/20181,537-24930Exceed
26/10/20181,538-25190Exceed
28/11/20181,5391000310Within Limit
28/11/20181,540310Within Limit
09/12/20181,541244Within Limit
17/12/20181,542-272Exceed
31/12/20181,543-13074Exceed
31/12/20181,544-13154Exceed
02/01/20191,545-13177Exceed
21/01/20191,546986823Within Limit
21/01/20191,547883787Within Limit
25/01/20191,548883528Within Limit
27/01/20191,549-116472Exceed
27/01/20191,550-121650Exceed
31/01/20191,551-627487Exceed
04/02/20191,552-1357780Exceed
05/02/20191,553-1358615Exceed
18/02/20191,554-1358613Exceed
20/02/20191,555-358613Exceed
20/02/20191,556321387Within Limit
21/02/20191,557321072Within Limit
03/03/20191,559-684873Exceed

 

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 :

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

 

 

Thank you

 

 

 

1 Solution

Accepted Solutions
Kushal_Chawda

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;

 

Annotation 2020-08-13 102453.png

View solution in original post

6 Replies
sunny_talwar

Have you looked into doing an Intervalmatch 

Kushal_Chawda

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;

 

Annotation 2020-08-11 134637.png

ch_riadh
Partner
Partner
Author

Thank you, Just to clarify.

I have a transaction table with 8 fields:

TRS_NOSEQUENCETRANS_DATETRANS_AMOUNTBALANCEAccountNoTYPE
101100111/13/2020100010001001ABC
102100211/13/2020200020001002XYZ
103100221/15/202090011001002XYZ
104100121/17/202040014001001ABC
105100231/17/2020110001002XYZ
106100131/17/2020-1600-2001001ABC
107100241/19/2020200-2001002XYZ
108100141/20/202020001001ABC


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

This is the output i want :

TRS_NOSEQUENCESEQUENCE2TRANS_DATETRANS_AMOUNTBALANCEBALANCE2AccountNoTYPE
10110011100111/13/20201000100010001001ABC
10210021100211/13/20202000200020001002XYZ
NULLNULL100111/14/2020NULLNULL10001001ABC
NULLNULL100211/14/2020NULLNULL20001002XYZ
NULLNULL100111/15/2020NULLNULL10001001ABC
10310022100221/15/2020900110011001002XYZ
NULLNULL100111/16/2020NULLNULL10001001ABC
NULLNULL100221/16/2020NULLNULL11001002XYZ
10210012100121/17/2020400140014001001ABC
10310013100131/17/2020-1600-200-2001001ABC
10510023100231/17/20201100001002XYZ
NULLNULL100131/18/2020NULLNULL-2001001ABC
NULLNULL100231/18/2020NULLNULL01002XYZ
NULLNULL100131/19/2020NULLNULL-2001001ABC
10710024100241/19/2020200-200-2001002XYZ
10410014100141/20/2020200001001ABC

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

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

ch_riadh
Partner
Partner
Author

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

Thank you

Kushal_Chawda

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;

 

Annotation 2020-08-13 102453.png