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: 
ss2q
Contributor III
Contributor III

I want help with master calender

I'm trying to make a master calendar which links gl_date from the invoice table with the ordered date in the order table but when I create a data bridge it shows me 4 tables why is it? After this, I don't how to make the master calendar code. Anyone who can help me make the code or fix it? I just want the year and month only in canonical date calendar or master calender. I want to use year from gl_Date  and filter my results based on that. The common order number in both tables

 

[Data Bridge]:
 
Load
[ORDER_NO-ORDER_NUMBER],
[ORDERED_DATE] as CanonicalDate
 
Resident XX_OE_ORDER_LINES_TRIMMED2;
 
Load
[ORDER_NO-ORDER_NUMBER],
GL_DATE AS CanonicaDate
 
Resident XX_INVOICE_GL_MV_TRIMMED;
3 Replies
robert99
Specialist III
Specialist III

@ss2q 

The above won't work.

There are no short cuts with this. Its a quite advanced area of Qlik and hard to get it working as it should. You need to follow the steps as set out in the Canonical date thread 

The first step is making sure your order Table links (associations) are correct. So you must link the OrderLine table and OrderInv table correctly

What I usually do is concatenate the OrderLine, OrderDelivery and OrderInv tables and then Join Keep (by associated fields) to the Canonical Bridge. But don't suggest you do this for this example. Understand this set-up first.

 

ss2q
Contributor III
Contributor III
Author

@robert99 

For example, If I bring the order header, order line and order invoice table and make the association of the order header ID - Order line head ID and then with the Order line Order number - order invoice Order number and line ID of Order line and Line ID of invoice how will I make the master calendar after that to use year for the filter. but I want the year to be used from GL_date. It will be great if you could make the code and help me understand it step by step.

robert99
Specialist III
Specialist III

hello @ss2q 

Too busy at present. But if you still need help I will be free in a few days

Give it a go but the tables associations must be correct. Like for example

OrderHeader associated with OrderLines, Using field OrderNumber

OrderLines associated with OrderInvoice | Using fields --> OrderNumber & OrderLin

OrderInvoices associated with CanonicalTable | Using Qlik fields --> OrderNumber & rowno()

CanonicalTable associated with Calendar, | Using CanonicalDate