topic Re: Creating a Master Calendar using multiple dates in QlikView

Creating a Master Calendar using multiple dates

Anonymous — Wed, 22 Aug 2012 20:04:25 GMT

I have searching the community with no luck. I need to create a master calendar using 3 date fields. I have tried the Link table method but get the loop error. Can anyone suggest anything else I could try? I have attached my QVW.

Re: Creating a Master Calendar using multiple dates

agilos_mla — Wed, 22 Aug 2012 20:43:37 GMT

Hello,

What do you want to achivev exactly? Have one ReportMonthYear being sync between the 3 "cases" tables, simply do :

LK_Table:

Load ReportMonthYear, LOCATION_ID, CATEGORY_ID, autonumberHash128(ReportMonthYear&LOCATION_ID& CATEGORY_ID) AS LK_Key resident Cases;

Concatenate(LK)

Load ReportMonthYear, LOCATION_ID, CATEGORY_ID, autonumberHash128(ReportMonthYear&LOCATION_ID& CATEGORY_ID) AS LK_Keyresident IncidentCases;

Concatenate(LK)

Load ReportMonthYear, LOCATION_ID, CATEGORY_ID, autonumberHash128(ReportMonthYear&LOCATION_ID& CATEGORY_ID) AS LK_Key resident RecoveryCases;

and drop the 3 fields in the original tables.

hope it helps,

Re: Creating a Master Calendar using multiple dates

sebastiandperei — Wed, 22 Aug 2012 21:58:38 GMT

Why do you need 3 calendars?

Re: Creating a Master Calendar using multiple dates

Anonymous — Thu, 23 Aug 2012 12:10:39 GMT

Thanks so much for you suggestion. It looks like your approach would get rid of my synthetic keys and this is great. I am going to try this code this morning.

Re: Creating a Master Calendar using multiple dates

Anonymous — Thu, 23 Aug 2012 12:13:34 GMT

Hi Sebastian, I would really like to have one calendar that links to all three dates. I would like to be able to drill down and see detail data for specific years, quarters, months, day, etc.

Re: Creating a Master Calendar using multiple dates

masha-ecraft — Thu, 23 Aug 2012 12:54:26 GMT

Hi,

Here I posted sample code for linking 2 days to a calendar: http://community.qlik.com/message/250459#250459

Hope that could help you create a link to 3 dates as well.

/Masha

Re: Creating a Master Calendar using multiple dates

Anonymous — Thu, 23 Aug 2012 13:26:41 GMT

Hi Masha,

Thanks for the suggestion. The issue I am having is that the date fields are from 3 different fact tables. If they were all in 1 fact table, I think your code would work perfectly.

Temieka

Re: Creating a Master Calendar using multiple dates

masha-ecraft — Fri, 24 Aug 2012 06:29:53 GMT

Hi Temieka,

I suggest that you concatenate your 3 fact tables into one with just one date field and a field for a record type that will indicate from which original fact table each record is originating.

RecordType field is convenient to have to be able to filter your records.

FactTable:

LOAD

....

Date1 as Date,

....

Resident Fact1;

concatenate (FactTable)

LOAD

...,

Date2 as Date,

...

Resident Fact2;

Similar code for Fact3.

Drop Tables Fact1, Fact2, Fact3.

This way you can not only combine dates into one field for common analysis, but also put some other dimensions you are using in the application into fields with the same names.

Regards,

Masha

Re: Creating a Master Calendar using multiple dates

Fri, 24 Aug 2012 08:46:18 GMT

Hi,
As you done MasterCalendar using autogenerate calender is correct. use same Calender to link your all fact table using

CalendarDate key

Re: Creating a Master Calendar using multiple dates

Anonymous — Fri, 24 Aug 2012 11:15:33 GMT

Masha,

Your approach makes perfect sense. If I were to make one fact table that would also get rid of my synthentic keys. I am going to play around with your approach today. Thanks again for your help.

Temieka

Re: Creating a Master Calendar using multiple dates

jmmayoral3 — Wed, 28 Jan 2015 16:18:57 GMT

You have to create a Canonical Calendar.

View this entry because solution is here "Canonical Date"