Duplicate rows from interval date fields

[Théo]

Hi everybody, I need your help!

I have a table with so many records like this:

ID             ,          Start_Date,            End_Date

Key1234,          01/03/2021,           04/03/2021

Key5678,         28/02/2021,           02/03/2021

And I would like to obtain:

Key1234, 01/03/2021

Key1234, 02/03/2021

Key1234, 03/03/2021

Key1234, 04/03/2021

Key5678, 28/02/2021

Key5678, 01/03/2021

Key5678, 02/03/2021

I mean, I would like to multiply/duplicate this line by the number of days between the interval of dates and for this example, obtain 4 records.

I tried to create a calendar with al the dates from 2019 to 2022 and use a left join and intervalmatch function to associate the two dates on the interval but there is too many records and the data load editor need too much time to load the data...

Can someone help me please ? Thanks in advance

[Saravanan_Desingh · Accepted solution]

Try this,

SET DateFormat='DD/MM/YYYY';

tab1:
LOAD *, Date(Start_Date+IterNo()-1) As Date
While Start_Date+IterNo()-1<=End_Date;
LOAD * INLINE [
ID, Start_Date, End_Date
Key1234, 01/03/2021, 04/03/2021
Key5678, 28/02/2021, 02/03/2021
];

[Théo]

It's working! Thank you!