Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
harson
Contributor III
Contributor III

Populate date between 2 dates

Hello,

Any advice appreciated.

I have data like below:

IDCreateDateClosedDateStatus
VR0000101/09/2020 Open
VR0000201/09/202005/09/2020Closed
VR0000305/09/202006/09/2020Closed
VR0000405/09/202005/09/2020Closed

 

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 :

IDDateStatus
VR0000101/09/2020Open
VR0000102/09/2020Open
VR0000103/09/2020Open
VR0000104/09/2020Open
VR0000105/09/2020Open
VR0000201/09/2020Open
VR0000202/09/2020Open
VR0000203/09/2020Open
VR0000204/09/2020Open
VR0000205/09/2020Closed
VR0000305/09/2020Open
VR0000306/09/2020Closed
VR0000405/09/2020Closed

 

Cheers,

1 Solution

Accepted Solutions
Taoufiq_Zarra

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

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

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

View solution in original post

1 Reply
Taoufiq_Zarra

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

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

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