multiple fact tables with multiple dates

[#1]

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

[#2]

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.

[martynlloyd]

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.

[MK9885]

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.

[thomaslg_wq]

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.

[martynlloyd]

Hi Neda,

This will help you : Creating Reference Dates for Intervals

Regards,

Marty.

[simenkg · Accepted solution]

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

[#8]

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;

[thomaslg_wq]

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;

[#10]

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

[#11]

Thanks All,

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