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)
19 Replies
Reddeer10
Contributor III
Contributor III
Author

2222.pngdates are match but dublicate date and not comes orderid

Gabbar
Specialist
Specialist

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

 

Reddeer10
Contributor III
Contributor III
Author

 
Gabbar
Specialist
Specialist

It is Showing data in this way

Gabbar_0-1677240512970.png

Could you please resend it in XLSX format.

 

Reddeer10
Contributor III
Contributor III
Author

 
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

Reddeer10
Contributor III
Contributor III
Author

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

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

 

Reddeer10
Contributor III
Contributor III
Author

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.