Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
hic
Former Employee
Former Employee

 

A common situation when loading data into a Qlik document is that the data model contains several dates. For instance, in order data you often have one order date, one required date and one shipped date.

 

Base model.png

 

This means that one single order can have multiple dates; in my example one OrderDate, one RequiredDate and several ShippedDates - if the order is split into several shipments:

 

Logic 1.png

 

So, how would you link a master calendar to this?

 

Well, the question is incorrectly posed. You should not use one single master calendar for this. You should use several. You should create three master calendars.

 

The reason is that the different dates are indeed different attributes, and you don’t want to treat them as the same date. By creating several master calendars, you will enable your users to make advanced selections like “orders placed in April but delivered in June”. See more on Why You sometimes should Load a Master Table several times.

 

Your data model will then look like this:

 

Model with spec calendars.png

 

But several different master calendars will not solve all problems. You can for instance not plot ordered amount and shipped amount in the same graph using a common time axis. For this you need a date that can represent all three dates – you need a Canonical Date. This is how you create it:

 

First you must find a table with a grain fine enough; a table where each record only has one value of each date type associated. In my example this would be the OrderLines table, since a specific order line uniquely defines all three dates. Compare this with the Orders table, where a specific order uniquely defines OrderDate and RequiredDate, but still can have several values in ShippedDate. The Orders table does not have a grain fine enough.

 

This table should link to a new table – a Date bridge – that lists all possible dates for each key value, i.e. a specific OrderLineID has three different canonical dates associated with it. Finally, you create a master calendar for the canonical date field.

 

Full model.png

 

You may need to use ApplyMap() to create this table, e.g. using the following script:

 

     DateBridge:
     Load
          OrderLineID,
          Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate,
          'Order' as DateType
          Resident OrderLines;

     Load
          OrderLineID,
          Applymap('OrderID2RequiredDate',OrderID,Null()) as CanonicalDate,
          'Required' as DateType
          Resident OrderLines;

     Load
          OrderLineID,
          ShippedDate as CanonicalDate,
          'Shipped' as DateType
          Resident OrderLines;

 

If you now want to make a chart comparing ordered and shipped amounts, all you need to do is to create it using a canonical calendar field as dimension, and two expressions that contain Set Analysis expressions:

 

     Sum( {$<DateType={'Order'}>} Amount )
     Sum( {$<DateType={'Shipped'}>} Amount )

 

Bar chart.png

 

The canonical calendar fields are excellent to use as dimensions in charts, but are somewhat confusing when used for selections. For this, the fields from the standard calendars are often better.

 

Summary:

  • Create a master calendar for each date. Use these for list boxes and selections.
  • Create a canonical date with a canonical calendar. Use these fields as dimension in charts.
  • Use the DateType field in a Set Expression in the charts.

 

A good alternative description of the same problem can be found here. Thank you, Rob, for inspiration and good discussions.

 

HIC

192 Comments
kkhozema
Contributor II
Contributor II

What am I doing wrong

I am getting 2 tables, DateBridge and Link-1 table. Please help.

DateBridge:
Load
%Link,
Date(Floor([LINK.PO_REQUISITION_HEADERS_ALL.APPROVED_DATE])) as CanonicalDate,
'Req' as DateType
Resident Link;


// Concatenate

Load
%Link,
Date(Floor([LINK.PO_HEADERS_ALL.FIRST_APPROVAL_DATE])) as CanonicalDate,
'PO' as DateType
Resident Link;

 

 

0 Likes
1,271 Views
robert99
Specialist III
Specialist III

Hi @kkhozema 

Try adding

Concatenate (DateBridge)

Load

etc

to the second load

 

1,264 Views
kkhozema
Contributor II
Contributor II

It didn't work, that gives a single table but 2 different date columns

DateBridge.CanonicalDate

Link-1.CanonicalDate

0 Likes
1,308 Views
barnabyd
Partner - Creator III
Partner - Creator III

G'day @kkhozema ,

This indicates that you have a previous 'qualify' statement that is still active.

Cheers,

Barnaby.

1,289 Views
Qlik-Developer
Creator
Creator

Hi Henric,

Can you please provide me the complete code for creating the mapping tables?

 fetching the same table three times with same filed but with different querry,for Toatl defcts ,open defects,closed defects .

Total:

LOAD
Key,
Summary,
Status,
Assignee,
Reporter,
Created,
Updated,
Resolved

from project in (UCDK) AND issuetype != Sub-task AND status not in (Done,Closed);

Open:

LOAD
Key,
Summary,
Status,
Assignee,
Reporter,
Created,
Updated,
Resolved

from project in (UCDK) AND issuetype != Sub-task AND created > -1w ORDER BY created ASC;

Close:

LOAD
Key,
Summary,
Status,
Assignee,
Reporter,
Created,
Updated,
Resolved

from project in (UCDK) AND issuetype != Sub-task AND Status in (Done, Closed) AND (updated > -1w OR resolved > -1w) ORDER BY updated DESC;
 
How can i show total, open, close in one chart?
Total ,open by using created date and close should calculate form Resolved date.
1,160 Views
robert99
Specialist III
Specialist III

Hi @Qlik-Developer 

Why not simple concatenate the three tables

Or just load once with a new Type field based on Status

Then you could either

Use Canonical dates

or (what i would maybe do)

Add another field for ChartDate. Where the date would be added based on the appropriate Status

 

 

 

 

1,138 Views
dhasharadh
Creator III
Creator III

Thanks Much @Hicao .

I have the 3 different dates (request inflow, Order creation and deactivation) and including the activation date I have 4 different dates in a single fact table. it is making my reporting complex.

can I adapt the above method. and other important thing is I do not have any primary key in the fact table. every column have duplicates to some extent. but I can create a composite key by combining 2 or 3.

is it mandatory to have primary key like OrderLineID

828 Views
barnabyd
Partner - Creator III
Partner - Creator III

G'day @dhasharadh,

Yes, you can get use this technique with four dates. As they are all in the same fact table, it makes the code easier than in Henric's example above - you won't need the Applymap().

Regarding the composite key, it is possible to join two tables together on multiple columns but this creates a synthetic key table and complicates the data model. So I think it is always a good idea to create a unique composite key for your fact tables.

You just need four statements like this ...

     DateBridge:
     Load
          YourCompositeKey,
          ActivationDate as CanonicalDate,
          'Activated' as DateType
          Resident YourFactTable;

I hope this helps. Cheers, Barnaby.

784 Views
khadar
Contributor III
Contributor III

Hi Sir,

Thank you for sahring the  gteat post. I guess above will work, if one table have different date feilds with common feild  for the dates, howevery if I need to link the date columns from multiple tables which are not having common column in between. in this senior what will be the process ?

745 Views
barnabyd
Partner - Creator III
Partner - Creator III

G'day Khadar,

I'm thinking that the DateBridge table always needs to connect to the main fact table. If you look back at Henric's example, I think OrderLines is his Fact table and Orders is the Dimension. The other way of thinking about it is that you link the DateBridge to the table with the lowest granularity.

I hope this helps. Cheers, Barnaby.

689 Views