Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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.
show only times
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.
i try but.
actually my real problem is, how to associating master calendar and orders with order date (timestamp)
So you want to keep your order date in timestamp(hh:mm:ss) format and calendardate field in hh:mm format?
no forget this script. my order date like '2017-10-02 11:07:15' . how to create master calendar and Associating this order table ?
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);
i was try this but the memory was not enough. i filter date and try again.
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);