Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jason_nicholas
Creator II
Creator II

Master Calendar Function with Multiple Date Types

 

  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?

 

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

for reference you can take a look at this also:

Canonical Date

View solution in original post

17 Replies
YoussefBelloum
Champion
Champion

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

jason_nicholas
Creator II
Creator II
Author

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?

YoussefBelloum
Champion
Champion

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 ?

YoussefBelloum
Champion
Champion

for reference you can take a look at this also:

Canonical Date

sunny_talwar

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

jason_nicholas
Creator II
Creator II
Author

That makes a lot of sense. Thanks for the help

YoussefBelloum
Champion
Champion

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

sunny_talwar

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

YoussefBelloum
Champion
Champion

yes