Is this Associating Master Calendar and Fact tables correct ?

[Reddeer10]

Hey,

i create master calender and i associating Master Calendar with Order Table. But not match month,day...

what am i doing wrong?

mastercalendar script ; 

MinMaxDate:
LOAD
Min(CalenderKeyField) as MinDate,
Max(CalenderKeyField) as MaxDate
RESIDENT ORDERS;

Let vMinDate = peek('MinDate', 0, 'MinMaxDate');
Let vMaxDate = peek('MaxDate', 0, 'MinMaxDate');
Let vDays = vMaxDate - vMinDate +1;

DROP TABLE MinMaxDate;

TimeCalendarField:
Load
date(($(vMinDate) + (recno()/24/60)-1),'YYYY-MM-DD hh:mm') AS TempDate
// num(date(($(vMinDate) + (recno()/24/60)-1),'YYYY-MM-DD hh:mm')) AS CalenderKeyField
// Autogenerate 24 * 60 * 60;
// AutoGenerate(1) While Date($(vMinDate),'YYYY-MM-DD hh:mm:ss') <= Date($(vMaxDate),'YYYY-MM-DD hh:mm:ss');
AutoGenerate $(vDays)*24*60+1;

MasterCalendar:
Load
TempDate as CalenderKeyField,
Date(TempDate) as OrderDate,
Year(TempDate) as Year.OrderDate,
'Q'&Ceil(Month(TempDate)/3) as Quarter.OrderDate,
Week(TempDate) as Week.OrderDate,
Month(TempDate) as Month.OrderDate,
Day(TempDate) as Day.OrderDate,
WeekDay(TempDate) as DayName.OrderDate,
If(Year(TempDate)='2017',1,0) as '2017_Flag',
If(Month(TempDate)='Jan',1,0) as 'Jan_Flag',
If(Month(TempDate)='Feb',1,0) as 'Feb_Flag'
Resident TimeCalendarField
Order By TempDate ASC;

DROP TABLE TimeCalendarField;

orderqvdread script;

[ORDERS]:
LOAD
order_id,
customer_id,
order_status,
Date(order_purchase_timestamp,'YYYY-MM-DD hh:mm') as CalenderKeyField,
order_purchase_timestamp,
// num(Date(order_purchase_timestamp,'YYYY-MM-DD hh:mm')) AS CalenderKeyField,
order_approved_at,
order_delivered_carrier_date,
order_delivered_customer_date,
order_estimated_delivery_date
FROM [lib://DataFiles/ORDERS.qvd]
(qvd);

[Reddeer10]

dates are match but dublicate date and not comes orderid

[Gabbar]

Can you share the sample dataset containing few rows in excel file, I will try it.

[Reddeer10]

https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce

[Gabbar]

It is Showing data in this way

Could you please resend it in XLSX format.

[Gabbar · Accepted solution]

Got the data from your link, Thanks for that,
Tried many method but none of those worked, found a method to work around it. 
Order:
First 1000
LOAD
order_id,
customer_id,
order_status,
order_purchase_timestamp,
num(timestamp(floor(timestamp(timestamp#(order_purchase_timestamp,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm'),1/(24*60)),'YYYY-MM-DD hh:mm')) as Test,
Left(order_purchase_timestamp,len(order_purchase_timestamp)-3) as CalenderKeyField,
order_approved_at,
order_delivered_carrier_date,
order_delivered_customer_date,
order_estimated_delivery_date
FROM [lib://DataFiles/olist_orders_dataset.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

MinMaxDate:
LOAD
Floor(num(Min(CalenderKeyField))) as MinDate,
floor(num(Max(CalenderKeyField))) as MaxDate
RESIDENT Order;

Let vMinDate = peek('MinDate', 0, 'MinMaxDate');
Let vMaxDate = peek('MaxDate', 0, 'MinMaxDate');
Let vDays = Floor(vMaxDate - vMinDate +1);
DROP TABLE MinMaxDate;

TimeCalendarField:
Load
recno(),
date(($(vMinDate) + (recno()/(24*60))-1),'YYYY-MM-DD hh:mm') AS CalenderKeyField
AutoGenerate ($(vDays)+1)*24*60;

Trying other ways to see if any how the time function can work on this.

IF THIS RESOLVES YOUR ISSUE PLEASE ACCEPT THIS AS AN ANSWER

[Reddeer10]

thank you so much for your time.

this looks like a resolve. I'll mark it as the correct answer when I'm sure. I am currently working on it.

[Gabbar]

Ok and I will also update if i find a way using time functions.

[Reddeer10]

Thanks a lot.

lastest version;

[ORDERS]:
LOAD
order_id,
customer_id,
order_status,
Left(order_purchase_timestamp,len(order_purchase_timestamp)-3) as CalenderKeyField,
order_purchase_timestamp,
order_approved_at,
order_delivered_carrier_date,
order_delivered_customer_date,
order_estimated_delivery_date
FROM [lib://DataFiles/ORDERS.qvd]
(qvd);

MinMaxDate:
LOAD
Floor(num(Min(CalenderKeyField))) as MinDate,
floor(num(Max(CalenderKeyField))) as MaxDate
RESIDENT ORDERS;

Let vMinDate = peek('MinDate', 0, 'MinMaxDate');
Let vMaxDate = peek('MaxDate', 0, 'MinMaxDate');
Let vDays = Floor(vMaxDate - vMinDate +1);
DROP TABLE MinMaxDate;

TimeCalendarField:
Load
recno(),
date(($(vMinDate) + (recno()/(24*60))-1),'YYYY-MM-DD hh:mm') AS CalenderKeyField
AutoGenerate ($(vDays)+1)*24*60;

MasterCalendar:
Load
CalenderKeyField,
Month(CalenderKeyField) as month_CalenderKeyField,
Year(CalenderKeyField) as Year.OrderDate,
'Q'&Ceil(Month(CalenderKeyField)/3) as Quarter.OrderDate,
Week(CalenderKeyField) as Week.OrderDate,
Month(CalenderKeyField) as Month.OrderDate,
Day(CalenderKeyField) as Day.OrderDate,
WeekDay(CalenderKeyField) as DayName.OrderDate,
If(Year(CalenderKeyField)='2017',1,0) as '2017_Flag',
If(Month(CalenderKeyField)='Jan',1,0) as 'Jan_Flag',
If(Month(CalenderKeyField)='Feb',1,0) as 'Feb_Flag'
Resident TimeCalendarField;

drop Table TimeCalendarField;

i removed mark 'include null values' for table and line chart its worked.