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

multiple fact tables with multiple dates

Dear All,

I am kind of new to Qlikview so any help here would be appreciated.

I have a scenario when one of my fact tables have start and end date and others have just single column dates. I need to connect all to a master calendar. I have managed to use interval match to connect one of the fact tables to the master calendar but struggling on how to link the rest of them without creating any loop. below is the current DM:

SI_v3.png

As you see above effective_start_date and effective_end_date in SourceData_Accont have been matched to master calendar. I also need to link EFFECTIVE_DATE in SourceData_Transaction , AuA_EFFECTIVE_DATE in SourceData_AuA and EraliestOpen/ LatestClose in SourceData_Account to master calendar as well. I have tried creating a link table but it creates a loop .

Any suggestion in improving the data model would be welcome.

Thanks in advance

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

the way to handle multiple fact tables in QlikView with common dimensions is to concatenate them and separate the data with a "DataType" field. This is so that you dont get circular references that will ruin your model.

Fact:

load *, 'FileA' as FactType from fileA.qvd (qvd);

Concatenate(Fact)

load *, 'FileB' as FactType from FileB.qvd (qvd);

For the Date problem; I would use the single date as both the from date and the to date. Then the intervalmatch will work.

Fact:

load *, 'FileA' as FactType from fileA.qvd (qvd);

Concatenate(Fact)

load *,EFFECTIVE_DATE as [Effective Start Date], EFFECTIVE_DATE as [Effective End Date], 'FileB' as FactType from FileB.qvd (qvd);

View solution in original post

19 Replies
martynlloyd
Partner - Creator III
Partner - Creator III

Hi Neda,

Does this help? Canonical Date

regards,

Marty.

thomaslg_wq
Creator III
Creator III

Hi,

You may need to have only one fact table, and get all dates fields in this table.

I don't think you really need to use a link table in your case.

Use some join and create that only one fact table.

Then, when you'll get every dates in the same fact table, use canonical calendar - Really great post : Canonical Date

BR,

Thomas

MK9885
Master II
Master II

I'd take DateID as key to link Master with other date tables.

trim(date(YourDateFieldhere,'YYYYMMDD'))as [DateID],

Use this in your Master Calendar and Source.
For Master Calendar your Date Field might be Temp Date and in Source Table it can be Either Start Date or End Date.
It would be easy to solve if you can provide the sample data? But for Dates I'd use DateID as key.
Anonymous
Not applicable
Author

I believe this example can help you. You do not need to have all the information linked together.

engishfaque
Specialist III
Specialist III

Dear Neda,

I would suggest you to create Master Calendar,

Creating A Master Calendar

Master Calendar Generation Script

Kind regards,

Ishfaque Ahmed

Not applicable
Author

Thanks Thomas, I have tried to merge to one fact table but the problem is the table sizes as one can have 10million records and I might end up having more than 2billion if I join them.

Not applicable
Author

Thanks Marty, I have had a look at Canonical calendar. I have two issues to get it working for me:

  • start and end date are interval dates and can not be treated like separate dates e.g. for each account I need to pick the record where start and end dates falls within the selected date range. this is why I have used intervalmatch to link it to master calendar.
  • I don't have a table with'fine granularity' to be able to link to canonical calendar

any idea on how to cover the above with canonical date would be appreciated.

Not applicable
Author

Thanks Arvind, as mentioned above start and end date can not be treated like separate entities e.g. for each account I need to pick the record where start and end dates falls within the selected date range. this is why I have used intervalmatch to link it to master calendar.

Not applicable
Author

Thanks Marcio, I need to plot all my charts against a common date. that's why I need to link all to a master calendar.