Generate missing months

[surajap123]

Hi Experts,

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

Fill in missing dates till today

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!

[adamdavi3s]

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;

[surajap123]

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;

[rahulpawarb]

Hello Suraj,

Please refer attached sample application.

Regards!

Rahul