Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Reddeer10
Contributor III
Contributor III

Is this Associating Master Calendar and Fact tables correct ?

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

 

 

associate.jpgtable.jpg

 

Labels (6)
1 Solution

Accepted Solutions
Gabbar
Specialist
Specialist

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

View solution in original post

19 Replies
Gabbar
Specialist
Specialist

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
Contributor III
Contributor III
Author

show only times111.jpg

Gabbar
Specialist
Specialist

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
Contributor III
Contributor III
Author

i try but.

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

Gabbar
Specialist
Specialist

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

 

Reddeer10
Contributor III
Contributor III
Author

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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

 

Gabbar
Specialist
Specialist

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