Master Calendar Issue

Rehan — Mon, 22 Mar 2021 21:54:42 GMT

I have a sample data set attached. The script is below. I am trying to generate the missing dates using Master Calendar. I see the dates are generating fine but the new dates are not associated with the dimension. Please see the attached output and sample data.

What am i missing here.

Data:
LOAD
Product,
Date("Order Date",'MM/DD/YYYY') AS "Order Date",
Sales
FROM [lib://AttachedFiles/Sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min("Order Date") as minDate,
max("Order Date") as maxDate
Resident Data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

//EXIT SCRIPT;
MasterCalendar:
Load
TempDate AS "Order Date",
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

exit script;

Re: Master Calendar Issue

ArnadoSandoval — Wed, 24 Mar 2021 02:51:56 GMT

Hi @Rehan

It worked fine on my QS Desktop with your sample data, below its screenshot.

hope this helps,

Re: Master Calendar Issue

Rehan — Wed, 24 Mar 2021 13:10:59 GMT

NO it doesnt please see your screenshot. There are nulls in the "Product" field

topic Re: Master Calendar Issue in App Development

Master Calendar Issue

Re: Master Calendar Issue

Re: Master Calendar Issue