topic Re: Generate missing months in QlikView

Generate missing months

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have a requirement which matches with the below thread's solution(by Massimo).

The only difference is my source data has Month-Year instead of date. To explan my requirement, fo ArtNo that has no data for any month between its start and end month, we have to generate those months and peek the price of previous month.

Because i have monthly data i am not sure how to create a calender based on month-year(which will outer join to main table)

SOURCE:
load * inline [
ArtNo, Date, Price
57, 16/08/2015, 3.02
57, 01/10/2015, 2.97
57, 23/11/2015, 3.15
5, 01/08/2015, 3.02
5, 03/08/2015, 3.8
5, 01/09/2015, 2.97
5, 03/09/2015, 3.15
];

Aggr: //Date aggregated by month, which is my real structure.
LOAD ArtNo,
          Date(MonthName(Date),'MMM-YYYY') as Month,
          Sum(Price) as Price
Resident SOURCE
Group By ArtNo,
         Date(MonthName(Date),'MMM-YYYY');

Please help!

Re: Generate missing months

adamdavi3s — Fri, 31 Mar 2017 08:51:47 GMT

Sorry ignore that, didn't fully read the requirement, I think I get it now

How about something like:

MasterCalendar:

Load DISTINCT

Date(MonthName(Date),'MMM-YYYY') as Month

Year(TempDate) As Year,

'Q' & ceil(month(TempDate) / 3) AS Quarter;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('Date', recno()))-1 as mindate,

max(FieldValue('Date', recno())) as maxdate

AUTOGENERATE max(FieldValue('Date', recno())) - min(FieldValue('Date', recno()))-1;

Re: Generate missing months

surajap123 — Fri, 31 Mar 2017 09:33:13 GMT

Thanks for your reply.

As I mentioned, my requirement is to first find the min and max month of each ArtNo and then generate the missing once.

I created the script, but it seems my calendar is still behaving like dates, even though I used month.

SOURCE:
load * inline [
ArtNo, Date, Price
57, 16/08/2015, 3.02
57, 01/10/2015, 2.97
57, 23/11/2015, 3.15
5, 01/08/2015, 3.02
5, 03/08/2015, 3.8
5, 01/09/2015, 2.97
5, 03/09/2015, 3.15
];

Aggr:
LOAD ArtNo,
Date(MonthName(Date),'MMM-YYYY') as Month,
Sum(Price) as Price
Resident SOURCE
Group By ArtNo,
Date(MonthName(Date),'MMM-YYYY');

DROP Table SOURCE;

MinMaxDate:
Load ArtNo,
Min(Month) as MinMonth, Max(Month) as MaxMonth resident Aggr
Group By ArtNo;

Join (Aggr)
Load
ArtNo,
Date(iterno()+MinMonth) as Month
Resident MinMaxDate
While iterno()+MinMonth <= MaxMonth;

Re: Generate missing months

rahulpawarb — Fri, 31 Mar 2017 11:06:50 GMT

Hello Suraj,

Please refer attached sample application.

Regards!

Rahul