Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
craggus
Contributor III
Contributor III

populate table with all dates in a date range

Afternoon,

Looking for some assistance with a data load script in Qlik Sense please.

I am currently loading the following data into a table called 'ORDERS' via Qlik Sense script:

Order               Start Date               End Date

12345                 13/04/21                15/04/21

76854                 17/04/21                21/04/21

 

What I would like to do is to generate a new table using this data, with an entry for every date within the range (start to end). In the above example, the output would be:

Order           Date

12345           13/04/21

12345           14/04/21

12345           15/04/21

76854           17/04/21

76854           18/04/21

76854           19/04/21

76854           20/04/21

76854           21/04/21

 

How could I transform the data into this format from within the script?

 

Many Thanks

 

Craig

 

 

1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

 

Data:
LOAD * Inline [
Order,StartDate,EndDate
12345,13/04/21,15/04/21
76854,17/04/21,21/04/21
];

Data2:
LOAD Order,
Date(StartDate+IterNo()-1) As [Date]
Resident Data
While StartDate+IterNo()-1<= EndDate;

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

 

Data:
LOAD * Inline [
Order,StartDate,EndDate
12345,13/04/21,15/04/21
76854,17/04/21,21/04/21
];

Data2:
LOAD Order,
Date(StartDate+IterNo()-1) As [Date]
Resident Data
While StartDate+IterNo()-1<= EndDate;

QFabian
Specialist III
Specialist III

Hi @craggus , i made this, i just addes 2000 because of the year of your example Data:

Data:
LOAD * INLINE [
Order, StartDate, EndDate
12345, 13/04/21, 15/04/21
76854, 17/04/21, 21/04/21
];


Let vNoOfRows = NoOfRows('Data')-1;

For vRow = 0 to $(vNoOfRows)
vEndDate = peek('EndDate', $(vRow), 'Data')+2000;
vStartDate = peek('StartDate', $(vRow), 'Data')+2000;
vDays = ceil($(vEndDate) - $(vStartDate));
vOrder = peek('Order', $(vRow), 'Data');
For vDay = 0 to $(vDays)
Date:
Load
Order,
StartDate+$(vDay) as Date
Resident Data
Where
Order = $(vOrder);
Next
Next

QFabian
DutchArjo
Creator
Creator

This script/code give me an empty table 2 with only table headers.

 

I have something like the above but then I get all the dates between the startdate and the enddate, but also including 16/4/2014 which should not be in the list with dates.

 

load *, dual(date(DateNum, 'YYYYMMDD'), DateNum) as VisibleDate;
load *, date(DateNum) as DateKey;
load  $(vMinDate)+IterNo()-1 as DateNum
Autogenerate 1 While $(vMinDate)+IterNo()-1 <= $(vMaxDate);