Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hi @Rehan
It worked fine on my QS Desktop with your sample data, below its screenshot.
hope this helps,
NO it doesnt please see your screenshot. There are nulls in the "Product" field