Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Re: multiple fact tables with multiple dates

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.

Highlighted
Partner
Partner

Re: multiple fact tables with multiple dates

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.

Khan_Mohammed
Honored Contributor II

Re: multiple fact tables with multiple dates

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
Contributor III

Re: multiple fact tables with multiple dates

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.

Partner
Partner

Re: multiple fact tables with multiple dates

Hi Neda,

This will help you : Creating Reference Dates for Intervals

Regards,

Marty.

Partner
Partner

Re: multiple fact tables with multiple dates

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

Not applicable

Re: multiple fact tables with multiple dates

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
Contributor III

Re: multiple fact tables with multiple dates

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;

Not applicable

Re: multiple fact tables with multiple dates

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

Not applicable

Re: multiple fact tables with multiple dates

Thanks All,

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