topic Re: Count Days between field value in App Development

Count Days between field value

ch_riadh — Tue, 11 Aug 2020 09:08:39 GMT

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

Re: Count Days between field value

sunny_talwar — Tue, 11 Aug 2020 11:03:45 GMT

Have you looked into doing an Intervalmatch

Re: Count Days between field value

Kushal_Chawda — Tue, 11 Aug 2020 12:48:37 GMT

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;

Re: Count Days between field value

ch_riadh — Wed, 12 Aug 2020 11:23:13 GMT

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

Re: Count Days between field value

Kushal_Chawda — Wed, 12 Aug 2020 14:24:30 GMT

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

Re: Count Days between field value

ch_riadh — Thu, 13 Aug 2020 06:21:59 GMT

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

Thank you

Re: Count Days between field value

Kushal_Chawda — Thu, 13 Aug 2020 09:27:53 GMT

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;