MasterCalender per ID

[colinodonnel]

Hello there,

I have the following table:

ID	Min Date	Max Date
1	01/01/2018	05/01/2018
2	02/01/2018	05/01/2018

From this, how can I create the following table?:

ID	Date
1	01/01/2018
1	02/01/2018
1	03/01/2018
1	04/01/2018
1	05/01/2018
2	02/01/2018
2	03/01/2018
2	04/01/2018
2	05/01/2018

There are lots of posts on creating a Master Calendar so I have script to create between a single min/max date.

But the twist is,  I need the MasterCalendar script to repeat for each new ID, and to concatenate the results into one table.

Thank you,

Colin

[sunny_talwar · Accepted solution]

Assuming your Min and Max date are in format DD/MM/YYYY... you can try this

Table:
LOAD ID,
	 Date([Min Date] + IterNo() - 1) as Date
While [Min Date] + IterNo() - 1 <= [Max Date];
LOAD * INLINE [
    ID, Min Date, Max Date
    1, 01/01/2018, 05/01/2018
    2, 02/01/2018, 05/01/2018
];

[colinodonnel]

Hi Sunny,

Thanks for this.

What a beautifully succinct bit of code.

I don't fully understand how it works.

Would you kindly explain how / why it creates the new rows one ID at a time (a bit like a loop maybe?)

Thanks, Colin