Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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);
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
I'd take DateID as key to link Master with other date tables.
trim(date(YourDateFieldhere,'YYYYMMDD'))as [DateID],
I believe this example can help you. You do not need to have all the information linked together.
Dear Neda,
I would suggest you to create Master Calendar,
Master Calendar Generation Script
Kind regards,
Ishfaque Ahmed
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.
Thanks Marty, I have had a look at Canonical calendar. I have two issues to get it working for me:
any idea on how to cover the above with canonical date would be appreciated.
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.
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.