topic Re: Add Year/Month within date range(Qlikview) in App Development

Add Year/Month within date range(Qlikview)

Tugge1 — Tue, 07 Mar 2023 09:12:18 GMT

Hi, I need some help!

I have a script that creates a table showing the static quantity of a product in stock during a "from date" to a "to date", it looks like this:

Product	FromDate	ToDate	Quantity
1	2021-06-08	2021-10-14	20
1	2021-10-15	2021-11-10	25
1	2021-11-11	2022-02-04	27
1	2022-02-05	2022-02-23	4

But I need the script to add rows with year/month that fit into the range of each "FromDate" to "ToDate", still with the same stock quantity. And i also need a new column "QD" that shows the total of days in each month the product have been in stock, see example below. Is this possible to do?

Product	FromDate	ToDate	Quantity	YearMonth	QD
1	2021-06-08	2021-10-14	20	202106	23
1	2021-06-08	2021-10-14	20	202107	31
1	2021-06-08	2021-10-14	20	202108	31
1	2021-06-08	2021-10-14	20	202109	30
1	2021-06-08	2021-10-14	20	202110	14

Best regards/ Patric

Re: Add Year/Month within date range(Qlikview)

brunobertels — Tue, 21 Mar 2023 12:48:39 GMT

Try this

[Table]:
Load
Product,
Quantity,
FromDate,
ToDate,
MonthName(FromDate +IterNo()-1) as MonthYear
while FromDate + IterNo()-1<= ToDate;

Load
Product,
date(FromDate,'YYYY-MM-DD') as FromDate,
date(ToDate,'YYYY-MM-DD') as ToDate,
Quantity
;
LOAD * INLINE
[
Product,FromDate,ToDate,Quantity
1,2021-06-08,2021-10-14,20
1,2021-10-15,2021-11-10,25
1,2021-11-11,2022-02-04,27
1,2022-02-05,2022-02-23,4
](delimiter is ',');

You may get this kind of table

Add this as mesure to calcualte number of days :

if(MonthName(FromDate) = MonthYear ,monthend(FromDate)-FromDate,
if(MonthName(ToDate) = MonthYear ,day(ToDate),

day(monthend(MonthYear))))

Re: Add Year/Month within date range(Qlikview)

MarcoWedel — Tue, 21 Mar 2023 18:41:32 GMT

maybe one solution could be:

table1: LOAD *, Date(MonthStart(FromDate,IterNo()-1),'YYYYMM') as YearMonth, RangeMin(ToDate,DayStart(MonthEnd(FromDate,IterNo()-1)))-RangeMax(FromDate,MonthStart(FromDate,IterNo()-1))+1 as QD Inline [ Product,FromDate,ToDate,Quantity 1,2021-06-08,2021-10-14,20 1,2021-10-15,2021-11-10,25 1,2021-11-11,2022-02-04,27 1,2022-02-05,2022-02-23,4 ] While MonthStart(FromDate,IterNo()-1)<=ToDate;

hope this helps

Marco