Date loop with outstanding case

[qv_testing]

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..

[qv_testing]

Experts! can you please help me

[Anil_Babu_Samineni]

What are you trying here?

[qv_testing]

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!

[Anil_Babu_Samineni]

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.

[rwunderlich]

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