Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mccook
Partner - Creator
Partner - Creator

Populating Data Between a Date Range

Hi,

Any advice appreciated.

I have data like below:

UserStart DateEnd DateValue
Dennis01/11/202028/02/202115.10
Kelly01/12/202031/01/202126.20

 

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

UserStart DateEnd DateValueDate
Dennis 01/11/202028/02/202115.1001/11/2020
Dennis 01/11/202028/02/202115.1001/12/2020
Dennis 01/11/202028/02/202115.1001/01/2021
Dennis 01/11/202028/02/202115.1001/02/2021
Kelly01/12/202031/01/202126.2001/12/2020
Kelly01/12/202031/01/202126.2001/01/2021

 

Cheers,


Dean

Labels (4)
1 Solution

Accepted Solutions
Kushal_Chawda

@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];

View solution in original post

3 Replies
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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

@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
Partner - Creator
Partner - Creator
Author

Thanks, works great