topic Re: Creating a Data Model with Canonical Date in QlikView

Creating a Data Model with Canonical Date

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to create a new data model for my application that is robust. Currently I have loaded my master table twice, creating all my date time functions off of specific dates, but I cannot use a chart to sums, counts, against eachother across the same time frame.

I've been reading this post:

Canonical Date

about canonical dates, and believe this is a great method to accomplish this....but I'm a little fuzzy on how I do it.

Currently I have one concatenated table, that has three relevant dates, but no table with "a grain fine enough" like Henric suggests in the above reading..

Example Data:

SomeKey SomeOtherKey CreatedDate DueDate DateResolved

123 - 01/01/2016 01/31/2016 01/21/2016

456 - 01/05/2016 01/04/2016 -

- 987 01/02/2016 02/01/2016 01/14/2016

- 654 01/10/2016 02/09/2016 01/29/2016

This is a summation of my data. There will always be a Created and Due Date, but not always a DateResolved. Also, I have two keys because they are two different tables that cannot be joined.

How can I create a canonical date off of this? Would it be like the example data below?

Key Date DateType KeyType

123 01/01/2016 Created FirstKey

123 01/31/2016 Due FirstKey

123 01/21/2016 Resolved FirstKey

...

987 01/02/2016 Created SecondKey

987 02/01/2016 Due SecondKey

987 01/14/2016 Resolved SecondKey

....

And this table would work as my "Data Bridge" to my "Master Table" where the master table has each "Master Calendar" for each Date Type branched off. The "Data Bridge" would then be used to make the "Canonical Date" Master Calendar

I'm not the best data modeler so any feedback would be appreciated...

Re: Creating a Data Model with Canonical Date

swuehl — Thu, 24 Nov 2016 18:59:07 GMT

Create a primary key in your example data table, either by using the two key fields, or by just creating a Row number uisng

LOAD

RowNo() as RowID,

...

then use this key in your canonical date table (yes, it would look like this table, one record per date).

Re: Creating a Data Model with Canonical Date

lucasdavis500 — Thu, 24 Nov 2016 19:18:03 GMT

Then use the Pkey (i.e. RowNo()) as the link between the bridge and my master table?

Re: Creating a Data Model with Canonical Date

swuehl — Thu, 24 Nov 2016 19:23:35 GMT

The master table is your calendar? Then the key field between master table and bridge would be Date field.

Re: Creating a Data Model with Canonical Date

lucasdavis500 — Thu, 24 Nov 2016 19:26:33 GMT

sorry, no, but the new Pkey would be the link from the "bridge" table to my "master table" and then the generic "date" field would be the link between my "bridge table" and the "Canonical Date" Calendar table..

Re: Creating a Data Model with Canonical Date

swuehl — Thu, 24 Nov 2016 19:53:12 GMT

So, are there any open issues left?

Re: Creating a Data Model with Canonical Date

Anonymous — Thu, 22 Mar 2018 17:12:38 GMT

Hi Stefan Wühl,

Could you place a diagram (image model) of the finished model?

(With masterCalendar and canonical date)

I have the same question as Duke and try to use your solution to resolve it.

Thank you!

Re: Creating a Data Model with Canonical Date

swuehl — Thu, 22 Mar 2018 17:29:56 GMT

There are more informations and models in the blog post by HIC that is referenced in the original post.

or maybe have a look at

Linking to two or more dates