Populate date between 2 dates

harson — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Any advice appreciated.

I have data like below:

ID	CreateDate	ClosedDate	Status
VR00001	01/09/2020		Open
VR00002	01/09/2020	05/09/2020	Closed
VR00003	05/09/2020	06/09/2020	Closed
VR00004	05/09/2020	05/09/2020	Closed

And I need to populate data between CreateDate and ClosedDate. If ClosedDate is null, I take the current date as the closing date but keeping the status in "Open".

As an example of the expected result below, I took Date(Today())=05/09/2020

I need it to be like below :

ID	Date	Status
VR00001	01/09/2020	Open
VR00001	02/09/2020	Open
VR00001	03/09/2020	Open
VR00001	04/09/2020	Open
VR00001	05/09/2020	Open
VR00002	01/09/2020	Open
VR00002	02/09/2020	Open
VR00002	03/09/2020	Open
VR00002	04/09/2020	Open
VR00002	05/09/2020	Closed
VR00003	05/09/2020	Open
VR00003	06/09/2020	Closed
VR00004	05/09/2020	Closed

Cheers,

Re: Populate date between 2 dates

Taoufiq_Zarra — Thu, 08 Oct 2020 09:22:13 GMT

@harson If ClosedDate is null, I take the current date as the closing =>08/10/2020, if so , one solution:

Input: LOAD ID, CreateDate,Date(if(len(trim(ClosedDate))=0,today(),ClosedDate)) as ClosedDate , Status INLINE [ ID, CreateDate, ClosedDate, Status VR00001, 01/09/2020,, Open VR00002, 01/09/2020, 05/09/2020, Closed VR00003, 05/09/2020, 06/09/2020, Closed VR00004, 05/09/2020, 05/09/2020, Closed ]; Tmp: noconcatenate load ID,Date,if(Status='Closed' and Date<ClosedDate,'Open',Status) as Status; load *,Date(CreateDate+IterNo()-1) as Date resident Input while CreateDate+IterNo()-1<=ClosedDate; drop table Input;

output:

topic Re: Populate date between 2 dates in QlikView

Populate date between 2 dates

Re: Populate date between 2 dates