topic Re: multiple fact tables with multiple dates in QlikView

multiple fact tables with multiple dates

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: multiple fact tables with multiple dates

martynlloyd — Thu, 15 Dec 2016 16:26:06 GMT

Hi Neda,

Does this help? Canonical Date

regards,

Marty.

Re: multiple fact tables with multiple dates

thomaslg_wq — Thu, 15 Dec 2016 16:27:20 GMT

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

Re: multiple fact tables with multiple dates

MK9885 — Thu, 15 Dec 2016 16:28:41 GMT

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.

Re: multiple fact tables with multiple dates

Anonymous — Thu, 15 Dec 2016 16:40:58 GMT

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

Re: multiple fact tables with multiple dates

engishfaque — Thu, 15 Dec 2016 17:01:34 GMT

Dear Neda,

I would suggest you to create Master Calendar,

Creating A Master Calendar

Master Calendar Generation Script

Kind regards,

Ishfaque Ahmed

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 09:11:41 GMT

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.

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 09:17:43 GMT

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.

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 09:19:29 GMT

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.

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 09:20:43 GMT

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.

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 09:22:05 GMT

Thanks Ishfaque, I have already created a master calendar and you can see it in the data model. the issue is linking all dates to the master calendar.

Re: multiple fact tables with multiple dates

martynlloyd — Fri, 16 Dec 2016 12:50:19 GMT

Hi Neda,

My concern would be that if you could find a way to link everything, you would make selection very difficult in the UI, possibly misleading the user -

I suggest creating a unique key to sourcedata_account , and using autogenerate (or for loop) to create a table with individual rows for each date in the start-end range, linked to sourcedata_account with that key. Key could be ACCOUNTENTITYID & effective_start_date & effective_end_date...

(drop or rename the start & end dates to avoid an unwanted join).

Regards,

Marty.

Re: multiple fact tables with multiple dates

MK9885 — Fri, 16 Dec 2016 13:18:45 GMT

You'd just need to link one date field to Master Date (Effective Date) <<< Calendar dates from Master Table to your Start or End Date and the selection will reflect on your UI.

I had same Issue.

I had Start Date, End Date.

Here are few lines from my script..

SourceTable:

...

Dates(MyDateField) as Dates

....

From...

where not "MyDateField"='';

MasterCalendar:

trim(date(TempDate,'YYYY-MM-DD'))

as Dates,

.....

Resident TempCalendar;

TempDatesTable:

Load*

Resident MasterCalendar

WHERE not Exists(Dates);

I've just linked one field from Source Table to Master Table. You don;t have to link all Dates fields to Master. It can be only one. In this case Dates will be link field b/w source and Master.

Re: multiple fact tables with multiple dates

thomaslg_wq — Fri, 16 Dec 2016 13:31:32 GMT

Well, actually a workaround would be :

All dates fields in the linked_table, so it would be a temporary big table, but no choice

Each combination of dates fields should receive an ID in the linked_table:

Date1 / Date 2 / Period1 / ID

A / B / XY / 1

A / C / XZ / 2

A / B XY / 1

Then, create a Brdige for the canonical calendar:

ID / CalendarType / Date

1 / Date1 / A

1 / Date2 / B

1 / Period1 / Several rows from X to Y

etc.

Then, link your master calendar to ths bridge and in every measure you create in interface, select on what kind of calendar type you want the analysis to be one : sum({<CalendarTpe={'Date1'}>} RandomField)

And at the end, you should delete all dates fields from the link_table and do a Load distinct in order to delete duplicates in this table.

Re: multiple fact tables with multiple dates

martynlloyd — Fri, 16 Dec 2016 13:39:46 GMT

Hi Neda,

This will help you : Creating Reference Dates for Intervals

Regards,

Marty.

Re: multiple fact tables with multiple dates

simenkg — Fri, 16 Dec 2016 13:45:54 GMT

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);

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 15:38:19 GMT

Thanks All,

I have tried to create a link table with all dates using following script but get RAM error. It seems LEFT JOIN causing the issue as BridgeTable_1 returns 38million and joining it to 300,000 records in BridgeTable cause memory error. any help on how to improve the script would be really appreciated:

[tmp]:

LOAD

%ID,

'Account' as DateType,

DATE(FLOOR(effective_start_date)) AS date_from,

DATE(FLOOR(effective_end_date)) AS date_to

RESIDENT SourceData_Account;

CONCATENATE (tmp)

LOAD

TRANSACTION_ID,

'Transaction' as DateType,

DATE(FLOOR(EFFECTIVE_DATE)) AS date_from,

DATE(FLOOR(EFFECTIVE_DATE)) AS date_to

RESIDENT SourceData_Transaction;

CONCATENATE (tmp)

LOAD

AUA_ID,

'AuA' as DateType,

DATE(FLOOR(AuA_EFFECTIVE_DATE)) AS date_from,

DATE(FLOOR(AuA_EFFECTIVE_DATE)) AS date_to

RESIDENT SourceData_AuA;

[BridgeTable]:

NOCONCATENATE LOAD DISTINCT * RESIDENT tmp;

DROP TABLE tmp;

//QUALIFY *;

LEFT JOIN (BridgeTable)

INTERVALMATCH (Period)

LOAD date_from, date_to RESIDENT BridgeTable;

Re: multiple fact tables with multiple dates

thomaslg_wq — Fri, 16 Dec 2016 15:57:45 GMT

Try with this script :

// assuming a start from your initial picture data model without master calendar and bridge

// First join

left join(LinkTable)

LOAD

ACCOUNT_ENTITY_ID,

effective_start_date,

effective_end_date

resident SourceData_Account;

Drop field effective_start_date from SourceData_Account;

Drop field effective_end_date from SourceData_Account;

MinMax:

LOAD min(effective_start_date) as MinDate,

max(effective_end_date) as MaxDate

resident LinkTable;

let vMin=peek('MinDate',0,'MinMax');

let vMax=peek('MaxDate',0,'MinMax');

Drop table MinMax;

TempDates:

LOAD $(vMin)+iterno() as Effective_Date1

autogenerate 1 while $(vMin)+iterno()<$(vMax);

inner join(LinkTable)

intervalmatch(Effective_Date1)

LOAD distinct

effective_start_date,

effective_end_date

resident LinkTable;

Drop table TempDates;

Drop fields effective_start_date, effective_end_date;

// Second join

left join(LinkTable)

LOAD

Account_Instrument_ID,

AuA_EFFECTIVE_DATE as EffectiveDate2

resident SourceData_AuA;

Drop field AuA_EFFECTIVE_DATE from SourceData_AuA;

// Third join

left join(LinkTable)

LOAD

Account_Instrument_ID,

EFFECTIVE_DATE as EffectiveDate3

resident SourceData_Transaction;

Drop field EFFECTIVE_DATE from SourceData_Transaction;

// Creating Ids

NewLinkTable:

LOAD *,

autonumber(Effective_Date1&'/'&Effective_Date2&'/'&Effective_Date3) as %ID

resident LinkTable;

// Creating Bridge for canonical calendar

Bridge:

LOAD distinct

%ID,

Effective_Date1 as Date,

'Type1' as Calendar_Type

resident LinkTable;

concatenate(Bridge)

LOAD distinct

%ID,

Effective_Date2 as Date,

'Type2' as Calendar_Type

resident LinkTable;

concatenate(Bridge)

LOAD distinct

%ID,

Effective_Date3 as Date,

'Type3' as Calendar_Type

resident LinkTable;

Re: multiple fact tables with multiple dates

Fri, 16 Dec 2016 19:55:30 GMT

Hi Simen,

do you mean rename dates from FileB to match date names in FileA? in this case:

Concatenate(Fact)

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

Thanks

Neda

Re: multiple fact tables with multiple dates

Mon, 19 Dec 2016 12:04:15 GMT

Thanks All,

Managed to solve this by concatenating all fact tables and creating a bridge table for all dates using Martyn solution.