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);

[Gabbar]

I think your Calendar Key field which you created in Orders table also have seconds or miniseconds in it, due to which it it not linking properly

Try this in your orders table for creating CalenderKeyField
Time#(Text(Time(Frac(order_purchase_timestamp), 'h:mm TT')), 'h:mm TT') as CalenderKeyField

It it resolves your issue please accept this as answer.

[Reddeer10]

show only times

[Gabbar]

It will only show time as HH:MM but it wont store in the same format, it is not trimming them to minutes.
You need to trim out the seconds so that your fields can match.

It it resolves your issue please accept this as answer.

[Reddeer10]

i try but.

actually my real problem is, how to associating master calendar and orders with order date (timestamp)

[Gabbar]

So you want to keep your order date in timestamp(hh:mm:ss) format and calendardate field in hh:mm format?

[Reddeer10]

no forget this script. my order date like '2017-10-02 11:07:15' . how to create master calendar and Associating  this order table ?

[Gabbar]

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

and orders table:

[ORDERS]:
LOAD
order_id,
customer_id,
order_status,
Date(order_purchase_timestamp,'YYYY-MM-DD hh:mm:ss') 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);

[Reddeer10]

i was try this but the memory was not enough. i filter date and try again.

[Gabbar]

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

and orders table:

[ORDERS]:
LOAD
order_id,
customer_id,
order_status,
Timestamp(Floor(Date(order_purchase_timestamp))+Timestamp#(Text(TimeStamp(Frac(order_purchase_timestamp), 'YYYY-MM-DD hh:mm')), 'YYYY-MM-DD hh:mm'),'YYYY-MM-DD hh:mm') 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);