Populating Data Between a Date Range

[mccook]

Hi,

Any advice appreciated.

I have data like below:

User	Start Date	End Date	Value
Dennis	01/11/2020	28/02/2021	15.10
Kelly	01/12/2020	31/01/2021	26.20

And I need to flesh it out as above is a monthly values, so I need it to be like below:

User	Start Date	End Date	Value	Date
Dennis	01/11/2020	28/02/2021	15.10	01/11/2020
Dennis	01/11/2020	28/02/2021	15.10	01/12/2020
Dennis	01/11/2020	28/02/2021	15.10	01/01/2021
Dennis	01/11/2020	28/02/2021	15.10	01/02/2021
Kelly	01/12/2020	31/01/2021	26.20	01/12/2020
Kelly	01/12/2020	31/01/2021	26.20	01/01/2021

Cheers,

Dean

[Taoufiq_Zarra]

@mccook  like ?

Input:

LOAD * INLINE [
    User, Start Date, End Date, Value
    Dennis, 01/11/2020, 28/02/2021, 15.10
    Kelly, 01/12/2020, 31/01/2021, 26.20
];

left join 

load  distinct  User,Num(Month([Start Date]+ IterNo() - 1)) as DATETMP resident Input 

While (Year([Start Date] + IterNo() -1)*12+Num(Month([Start Date] + IterNo() -1))<=(Year([End Date])*12+Num(Month([End Date]))));

output:

[Kushal_Chawda · Accepted solution]

@mccook  try below

Data:
LOAD *, 
     MonthStart([Start Date],IterNo()-1) as Date
 Inline [
User	Start Date	End Date	Value
Dennis	01/11/2020	28/02/2021	15.10
Kelly	01/12/2020	31/01/2021	26.20 ] (delimiter is '\t')
while MonthStart([Start Date],IterNo()-1)<=[End Date];

[mccook]

Thanks, works great