topic Re: Populating Data Between a Date Range in QlikView

Populating Data Between a Date Range

mccook — Fri, 15 Nov 2024 23:51:29 GMT

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

Re: Populating Data Between a Date Range

Taoufiq_Zarra — Tue, 06 Oct 2020 13:35:20 GMT

@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:

Re: Populating Data Between a Date Range

Kushal_Chawda — Tue, 06 Oct 2020 13:46:18 GMT

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

Re: Populating Data Between a Date Range

mccook — Tue, 06 Oct 2020 14:26:49 GMT

Thanks, works great