topic Re: Total Days Based on Status in App Development

Total Days Based on Status

ch_riadh — Sun, 23 Aug 2020 07:00:18 GMT

HI,

I have this table:

AccountNo	StartDate	EndDate	Status	Days
1002	31/01/2020	03/02/2020	Exceed	4
1002	04/02/2020	10/03/2020	Exceed	36
1002	11/03/2020	11/03/2020	Exceed	1
1002	12/03/2020	16/03/2020	Exceed	5
1002	17/03/2020	17/03/2020	Exceed	1
1002	18/03/2020	06/04/2020	Normal	20
1002	07/04/2020	20/04/2020	Exceed	14
1002	21/04/2020	22/04/2020	Exceed	2
1002	23/04/2020	28/04/2020	Exceed	6
1002	29/04/2020	02/05/2020	Exceed	4
1002	03/05/2020	10/06/2020	Exceed	39
1002	11/06/2020	16/06/2020	Exceed	6
1002	17/06/2020	28/06/2020	Exceed	12
1002	29/06/2020	07/07/2020	Normal	9
1002	08/07/2020	04/08/2020	Normal	28
1002	05/08/2020	19/08/2020	Exceed	15

And i want to display this table:

AccountNo	StartDate	EndDate	Status	CumulDays
1002	31/01/2020	17/03/2020	Exceed	47
1002	18/03/2020	06/04/2020	Normal	20
1002	07/04/2020	28/06/2020	Exceed	83
1002	29/06/2020	04/08/2020	Normal	37
1002	05/08/2020	19/08/2020	Exceed	15

Thank You

Re: Total Days Based on Status

Taoufiq_Zarra — Sun, 23 Aug 2020 10:24:50 GMT

Maye be like :

Input: LOAD * INLINE [ AccountNo, StartDate, EndDate, Status, Days 1002, 31/01/2020, 03/02/2020, Exceed, 4 1002, 04/02/2020, 10/03/2020, Exceed, 36 1002, 11/03/2020, 11/03/2020, Exceed, 1 1002, 12/03/2020, 16/03/2020, Exceed, 5 1002, 17/03/2020, 17/03/2020, Exceed, 1 1002, 18/03/2020, 06/04/2020, Normal, 20 1002, 07/04/2020, 20/04/2020, Exceed, 14 1002, 21/04/2020, 22/04/2020, Exceed, 2 1002, 23/04/2020, 28/04/2020, Exceed, 6 1002, 29/04/2020, 02/05/2020, Exceed, 4 1002, 03/05/2020, 10/06/2020, Exceed, 39 1002, 11/06/2020, 16/06/2020, Exceed, 6 1002, 17/06/2020, 28/06/2020, Exceed, 12 1002, 29/06/2020, 07/07/2020, Normal, 9 1002, 08/07/2020, 04/08/2020, Normal, 28 1002, 05/08/2020, 19/08/2020, Exceed, 15 ]; Tmp: noconcatenate load * resident Input order by AccountNo,StartDate,EndDate; drop table Input; Data: noconcatenate load *,if(rowno()=1,1,if(peek(Status)=Status and AccountNo=peek(AccountNo),peek(IDtmp),peek(IDtmp)+1)) as IDtmp resident Tmp; drop table Tmp; Final: noconcatenate load AccountNo,Date(min(StartDate)) as StartDate,Date(max(EndDate)) as EndDate, Status,IDtmp,sum(Days) as CumulDays resident Data group by AccountNo,Status,IDtmp; drop table Data;

output :

you can remove the "Tmp:" part if the input table is already sorted.

Re: Total Days Based on Status

lironbaram — Sun, 23 Aug 2020 10:52:13 GMT

this script is an example to the calculations you need to do to get the results

Table1: load * inline [ AccountNo, StartDate, EndDate, Status, Days 1002, 31/01/2020, 03/02/2020, Exceed, 4 1002, 04/02/2020, 10/03/2020, Exceed, 36 1002, 11/03/2020, 11/03/2020, Exceed, 1 1002, 12/03/2020, 16/03/2020, Exceed, 5 1002, 17/03/2020, 17/03/2020, Exceed, 1 1002, 18/03/2020, 06/04/2020, Normal, 20 1002, 07/04/2020, 20/04/2020, Exceed, 14 1002, 21/04/2020, 22/04/2020, Exceed, 2 1002, 23/04/2020, 28/04/2020, Exceed, 6 1002, 29/04/2020, 02/05/2020, Exceed, 4 1002, 03/05/2020, 10/06/2020, Exceed, 39 1002, 11/06/2020, 16/06/2020, Exceed, 6 1002, 17/06/2020, 28/06/2020, Exceed, 12 1002, 29/06/2020, 07/07/2020, Normal, 9 1002, 08/07/2020, 04/08/2020, Normal, 28 1002, 05/08/2020, 19/08/2020, Exceed, 15 ]; Table2: load *, if(Previous(AccountNo)=AccountNo and Previous(Status)=Status, RangeSum(peek('accDays'),Days),Days) as accDays, if(Previous(AccountNo)=AccountNo and Previous(Status)=Status, peek('counter'),rangesum(peek('counter'),1)) as counter Resident Table1 order by AccountNo,StartDate; drop table Table1; Table3: load AccountNo, min(StartDate) as StartDate, max(EndDate) as EndDate, Status, max(accDays) as accDays Resident Table2 group by AccountNo,Status,counter; drop Table Table2;