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: 
Anonymous
Not applicable

Creating a Master Calendar using multiple dates

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.

10 Replies
agilos_mla
Partner - Creator III
Partner - Creator III

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,

sebastiandperei
Specialist
Specialist

Why do you need 3 calendars?

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

masha-ecraft
Partner - Creator
Partner - Creator

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

Anonymous
Not applicable
Author

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

masha-ecraft
Partner - Creator
Partner - Creator

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

Not applicable
Author

Hi,

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

 

CalendarDate key

Anonymous
Not applicable
Author

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