Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
temiekaclay
New Contributor III

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.

Tags (1)
10 Replies
agilos_mla
Contributor III

Re: Creating a Master Calendar using multiple dates

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
Valued Contributor

Re: Creating a Master Calendar using multiple dates

Why do you need 3 calendars?

temiekaclay
New Contributor III

Re: Creating a Master Calendar using multiple dates

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.

temiekaclay
New Contributor III

Re: Creating a Master Calendar using multiple dates

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
Contributor

Re: Creating a Master Calendar using multiple dates

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

temiekaclay
New Contributor III

Re: Creating a Master Calendar using multiple dates

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
Contributor

Re: Creating a Master Calendar using multiple dates

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

Re: Creating a Master Calendar using multiple dates

Hi,

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

 

CalendarDate key

temiekaclay
New Contributor III

Re: Creating a Master Calendar using multiple dates

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

Community Browser