topic Re: Master Calendar Function with Multiple Date Types in QlikView

Master Calendar Function with Multiple Date Types

jason_nicholas — Fri, 16 Feb 2018 15:19:37 GMT

Hi. I am looking to add a Master Calendar to an incident tracker application. I am familiar with a basic Master Calendar, where every key ID is associated with a single date (one event happens on one date, which associates cleanly to the calendar).

However, this new application has multiple different dates:

Created Date; Completed Date; Impact Start Date; Impact End Date, and others.

How can I associate all of these with a calendar? I would like any one of them to have access to the functions in the calendar (numbering months, weeks, and quarters, flagging year-to-date, etc). If I select a single date, I would like to see anything created on that date, completed on that date, or otherwise associated with that date.

Incidents:

LOAD

[Created Date],

[Completed Date],

[Impact Start Date]…….

FROM source.xlsx;

Then I use all of these combined to create a Min and Max date to build the calendar:

TempMinMaxDate1:

LOAD

MIN([Created Date]) as MinDateTemp,

MAX([Created Date] as MaxDateTemp,

RESIDENT Incidents;

LOAD

MIN([Completed Date]) as MinDateTemp,

MAX([Completed Date]) as MaxDateTemp

RESIDENT Incidents;

{and so on, joining all date functions into a single Min or Max field}

TempMinMaxDate:

LOAD

MIN (MinDateTemp) as MinDate,

Max)MaxDateTemp) as MaxDate

RESIDENT TempMinMaxDate1;

Giving me a single Min Date and MaxDate to feed the rest of the standard Master Calendar function. But this information is not associated with the source data any longer. Please see the attached PNG to show the resulting table structure

Can you offer a suggestion on how to tie this together, so that the calendar functions are related to the dates in the source material, regardless of the type of date?

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 15:26:44 GMT

Hi,

you can concatenate your different tables (which contains the different date type) and using a flag for each concatenation and renaming all these different date columns AS DATE, it should be ok

Re: Master Calendar Function with Multiple Date Types

jason_nicholas — Fri, 16 Feb 2018 15:29:08 GMT

Once I rename everything AS DATE, wouldn't I lose the date type data? ie- the ability to look at [Impact Start Date] separately from [Created Date]?

Each different date type actually comes from the same source data, so they don't require a concatenation.

EDIT: I think I may understand. Please let me know if I'm on the right track.

I could Resident Load each of these dates into their own table, and create a [Impact Start Date] flag for that table, and a [Created Date] flag for that table, and the same for every start and end date. Then, when I concatenate these, they will all be just [Date], but each one will be associated with a different flag.

One date associated with a number of flags will allow them to associate with each other, as well as with the calendar. Is this all correct?

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 16:13:46 GMT

Yes you have summarized well what I suggested.. but the problem with this methof is that It will duplicates lines, which can be a big problem if you already have a lot of data..

I'm sure that there is better ways to do this, maybe stalwar1‌ can give us his opinion about it ?

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 16:16:04 GMT

for reference you can take a look at this also:

Canonical Date

Re: Master Calendar Function with Multiple Date Types

sunny_talwar — Fri, 16 Feb 2018 16:20:31 GMT

I think Canonical date is the right way to go about this

Re: Master Calendar Function with Multiple Date Types

jason_nicholas — Fri, 16 Feb 2018 16:26:34 GMT

That makes a lot of sense. Thanks for the help

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 16:26:47 GMT

Can give us your opinion also about the solution suggested here please?

Re: Master Calendar Function with Multiple Date Types

sunny_talwar — Fri, 16 Feb 2018 16:32:39 GMT

Is this what you are referring to?

you can concatenate your different tables (which contains the different date type) and using a flag for each concatenation and renaming all these different date columns AS DATE, it should be ok

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 16:35:37 GMT

yes

Re: Master Calendar Function with Multiple Date Types

jason_nicholas — Fri, 16 Feb 2018 16:37:20 GMT

I was unable to get that to work. I pulled each date into its own table like:

DateResident:

LOAD

'1' as [ImpactStartDateFlag],

[Impact Start Date] as DateTemp

RESIDENT Source;

CONCATENATE

1'' as [CreatedDateFlag],

[Created Date] as DateTemp

RESIDENT Source;

And it gave me three unrelated tables (Master Calendar, Source, and DateResident

I also tried OUTER JOIN instead of CONCATENATE, because I thought it would be better to just append additional columns for each flag to each relevant date. For some reason, this would freeze half-way through. When animating the debug, it seemed to process the first few and then just lock up.

Re: Master Calendar Function with Multiple Date Types

sunny_talwar — Fri, 16 Feb 2018 16:38:34 GMT

Isn't that the exact thing mentioned within canonical date?

DateBridge is a link table which is created by concatenating different dates from the table into a single table and all of them are named the CanonicalDate. OrderLineID is the unique identifier to tie it back to the main table. DataType is the flag. All like you mentioned

Re: Master Calendar Function with Multiple Date Types

sunny_talwar — Fri, 16 Feb 2018 16:39:52 GMT

Do you have a unique identifier within Incident table?

Re: Master Calendar Function with Multiple Date Types

jason_nicholas — Fri, 16 Feb 2018 16:47:03 GMT

I'm starting to realize this might be my missing piece. I have two data sources (two systems bringing in incident data). Each source has its own identifier, but source #2 also lists the ID from source #1 if it knows it (no guarantee). I am in the process of transforming the data from each of these so they combine into a single table. Unfortunately, they are pretty far apart at the source, so I've got my work cut out for me.

What I think I need to do is generate a separate key for the joined list. Once I have that, I will follow the Canonical Date instructions and see how it pans out.

As always, thanks for the help.

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 16:47:42 GMT

ok it is more clear now !

I always used this technique every time I had more than 1 type of date type in my model and I thought Canonical date was another different technique. (I know what is the main purpose, but never read the document)

Re: Master Calendar Function with Multiple Date Types

sunny_talwar — Fri, 16 Feb 2018 16:49:24 GMT

Happens to all of us... I am scared to go through the Hierarchy syntax... have implemented it 2-3 times but never really understood what is going on....

Re: Master Calendar Function with Multiple Date Types

YoussefBelloum — Fri, 16 Feb 2018 16:53:44 GMT

Scared you say ? Come on.. I feel dead sometimes

Re: Master Calendar Function with Multiple Date Types

sunny_talwar — Fri, 16 Feb 2018 17:16:53 GMT

Different unique identifiers should not be a problem.... AFAIK....