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

Total Days Based on Status

HI,

I have this table:

AccountNoStartDateEndDateStatusDays
100231/01/202003/02/2020Exceed4
100204/02/202010/03/2020Exceed36
100211/03/202011/03/2020Exceed1
100212/03/202016/03/2020Exceed5
100217/03/202017/03/2020Exceed1
100218/03/202006/04/2020Normal20
100207/04/202020/04/2020Exceed14
100221/04/202022/04/2020Exceed2
100223/04/202028/04/2020Exceed6
100229/04/202002/05/2020Exceed4
100203/05/202010/06/2020Exceed39
100211/06/202016/06/2020Exceed6
100217/06/202028/06/2020Exceed12
100229/06/202007/07/2020Normal9
100208/07/202004/08/2020Normal28
100205/08/202019/08/2020Exceed15

 

And i want to display this table:

AccountNoStartDateEndDateStatusCumulDays
100231/01/202017/03/2020Exceed47
100218/03/202006/04/2020Normal20
100207/04/202028/06/2020Exceed83
100229/06/202004/08/2020Normal37
100205/08/202019/08/2020Exceed15

 

Thank You

2 Replies
Taoufiq_Zarra

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 :

Capture.PNG

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
lironbaram
Partner
Partner

hi 

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;