Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
Why do you need 3 calendars?
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.
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.
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
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
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
Hi,
As you done MasterCalendar using autogenerate calender is correct. use same Calender to link your all fact table using
CalendarDate key
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