Iterno()

[ckandemir]

Hi Everyone,

I have ID, Start_Date and End_Date variables. I want to use Iterno() function to expand my table like table below.

Can anybody explain that how I can do that?

My table;

ID	Start_Date	End_Date
1	1.01.2020	3.01.2020
2	10.01.2020	12.01.2020
3	26.01.2020	27.01.2020

My result That I want;

ID	Date
1	1.01.2020
1	2.01.2020
1	3.01.2020
2	10.01.2020
2	11.01.2020
2	12.01.2020
3	26.01.2020
3	27.01.2020

[Vegar · Accepted solution]

Try this script:

SET DateFormat='D.MM.YYYY';
Load 
  ID,
  dayname(Start_Date - 1 + IterNo()) as Date
Inline [
  ID, Start_Date,End_Date
  1, 1.01.2020, 3.01.2020
  2, 10.01.2020, 12.01.2020
  3, 26.01.2020, 27.01.2020]
While
   Start_Date - 1 + IterNo() <= End_Date
;

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD ID,
	 Date(Start_Date + IterNo() - 1) as Date
While Start_Date + IterNo() - 1 <= End_Date;
LOAD * INLINE [
    ID, Start_Date, End_Date
    1, 1.01.2020, 3.01.2020
    2, 10.01.2020, 12.01.2020
    3, 26.01.2020, 27.01.2020
];

[Vegar]

It looks like I and @sunny_talwar  agrees on how to solve this. 

[sunny_talwar]

Yes we do 🙂