topic Re: Date loop with outstanding case in QlikView

Date loop with outstanding case

Nagaraju_KCS — Sat, 16 Nov 2024 01:43:33 GMT

Hi Community,

we have to Date loop based on outstanding case.

please have a look below screens

1. for ID = 1000, status has Qlikview and Closed - thus this is outstanding record.

which has the status updated to Closed, those records are outstanding.

so here, we have to loop start date (12/07/2019) to end date (26/09/2019) - value 250 should be in July, Aug and Sep

2. for ID=1001, status had Qlik sense and QS Pending - this is not a outstanding record.

we don't need to loop this record, status has still pending.

3. this is the outstanding record, status has updated closed.

this should be show in Aug, Sep and Oct month.

I have written script for date loop, but it is taking Jan to Dec. (I think we have to calculate Min and Max date, and group by with ID) - I tried, but No luck.

FOR i = 2019 to 2019
FOR k = 1 to 12
LET vDATE = MakeDate(i, k);
DATELOOP:
LOAD ID,
'$(vDATE)' as Date1
Resident Trans
Where Status='Closed';
NEXT k;
NEXT i;

Thanks In advance..

Re: Date loop with outstanding case

Nagaraju_KCS — Wed, 11 Dec 2019 14:33:09 GMT

Experts! can you please help me

Re: Date loop with outstanding case

Anil_Babu_Samineni — Wed, 11 Dec 2019 14:47:09 GMT

What are you trying here?

Re: Date loop with outstanding case

Nagaraju_KCS — Wed, 11 Dec 2019 15:17:59 GMT

Hi Anil,

Thanks for respond..

for ID-1000, my status has Qlikview and Closed (which i have highlighted in yellow, we have to loop between Months 7 to 9)

Please note which has the status updated to Closed, those records only we have to loop between months.

Status Qlikview has opened at 12/07/2019 and Closed at 26/09/2019 - we have missed Aug month

Output: we have to fill same record with Aug Month.

Same as 1005 example..

Hope you understand!

Re: Date loop with outstanding case

Anil_Babu_Samineni — Mon, 16 Dec 2019 11:24:23 GMT

Does it mean that, Whenever you have Qlikview and Status closed per ID you want generate missing dates? as an example, For ID = 1000, You want all dates or is that something missing months with previous record.

Re: Date loop with outstanding case

rwunderlich — Wed, 18 Dec 2019 19:14:09 GMT

I think it's:

Concatenate (Trans)
LOAD
ID,
Status,
1 as Generated, // For debugging
MonthStart(AddMonths(Date, IterNo())) as Date
Resident Trans
While MonthEnd(AddMonths(Date, IterNo())) < peek('Date', RecNo(), 'Trans')
And ID = peek('ID', RecNo(), 'Trans')
And peek('Status', RecNo(), 'Trans') = 'Closed'
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com