Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ];
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 ];
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