topic I want help with master calender in Featured Members

I want help with master calender

ss2q — Sat, 02 Mar 2024 19:34:04 GMT

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;

Re: I want help with master calender

robert99 — Sat, 02 Mar 2024 20:45:44 GMT

@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.

Re: I want help with master calender

ss2q — Sun, 03 Mar 2024 05:10:39 GMT

@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.

Re: I want help with master calender

robert99 — Mon, 04 Mar 2024 03:50:01 GMT

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