populate table with all dates in a date range

[craggus]

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

[jwjackso · Accepted solution]

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]

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

[DutchArjo]

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);