topic Re: Multiple fact tables in QlikView

Multiple fact tables

Wed, 19 Mar 2014 07:23:01 GMT

I have a script which includes the following:

TABLEA:

LOAD ''AP' as RecordType, A, B, C

Select A, B, C from SQLTABLEAP

CONCATENATE LOAD ''AD' as RecordType, A, B, C

Select A, B, C from SQLTABLEAD

TABLEB:

LOAD A, B, C, D, F

SQL exec QLIKVIEW_SP

Now when i look at the table view of the scipt, TABLEB has a link to TABLEA as there are fields which are common. However, I do not want this link to the TABLEA fact table as it is causing the results to be returned which i dont want.

Also, in the script I have dim tables for which TABLEA and TABLEB link which is what i do need so that is fine.

Please advise.

Re: Multiple fact tables

MK_QSL — Wed, 19 Mar 2014 07:25:57 GMT

NoConcatenate

TABLEB:

LOAD A, B, C, D, F

SQL exec QLIKVIEW_SP

Re: Multiple fact tables

Wed, 19 Mar 2014 07:43:13 GMT

will this then still link to my dimension tables and will it not cause synthetic keys?

Re: Multiple fact tables

Anonymous — Wed, 19 Mar 2014 07:52:39 GMT

First thing, You do not need to do force concatenate between tables coming from SQLTABLEAP and SQLTABLEAD sources, as columns in both the tables are exactly same. It would concatenate by default.

Second thing, If you do not want to link between TABLEA and TABLEB tables, you can qualify one of the table to avoid link or else rename the fields which are having same column names.

Hope it helps.

Smita

Re: Multiple fact tables

Wed, 19 Mar 2014 08:03:29 GMT

if i rename the fields with same column names how would i then be able to link them to my dim table?

Also can you give me an example f the qualify option.

Cheers

Re: Multiple fact tables

Anonymous — Wed, 19 Mar 2014 08:55:01 GMT

can you post your sample qvw document or script, so it would be easy to find out the solution ?

qualifying table example:

Qualify*;

Unqualify A; //////////////////// if you want to link the tables with column A. If not then, do not write this statement.

TABLEB:

LOAD A, B, C, D, F

SQL exec QLIKVIEW_SP

Unqualify*;

Re: Multiple fact tables

Thu, 20 Mar 2014 07:49:33 GMT

here is the script

LCFacts:

LOAD 'APC' AS RecordType, AJoinKey AS JoinKey, FinancialPeriod, LocalPointOfDelivery;

SELECT Top 2000 AJoinKey, FinancialPeriod, LocalPointOfDelivery,1 as RowCounter

FROM L_AFacts;

CONCATENATE LOAD

'OPA' AS RecordType,OJoinKey AS JoinKey, FinancialPeriod, LocalPointOfDelivery;

SELECT Top 2000 OPAJoinKey, FinancialPeriod,LocalPointOfDelivery

FROM PLC_OFacts;

LCostBuckets:

Load [Point of Delivery] AS LocalPointOfDelivery;

sql execute L_CostBucketsQlikView;

//Point of Delivery DIM

DIMPointofDelivery:

LOAD LocalPointOfDelivery, LocalPointOfDeliveryDescription AS PointofDeliveryDesc;

SQL SELECT *

From L_LocalPointsOfDelivery;

So in the example, what I expect to achieve is LCFacts to be linked to DIMPointof Delivery and LCostBuckets linked to DIMPointofDelivery. I do not want LCFacts and LCostBuckets to be linked.

Thanks

Re: Multiple fact tables

Anonymous — Thu, 20 Mar 2014 08:43:45 GMT

Create another key in DIMPointofDelivery table same as in LCostBuckets table to link the tables. Refer below edited query for your reference.

LCFacts:
LOAD 'APC' AS RecordType,
     AJoinKey AS JoinKey,
     FinancialPeriod,
     LocalPointOfDelivery;
SELECT Top 2000 AJoinKey, FinancialPeriod, LocalPointOfDelivery,1 as RowCounter
FROM L_AFacts;

//////////////////////you do not need to explicitly write concatenate statement, as it would do auto concatenate
//CONCATENATE
LOAD 'OPA' AS RecordType,
      OJoinKey AS JoinKey,
      FinancialPeriod,
      LocalPointOfDelivery;
SELECT Top 2000 OPAJoinKey, FinancialPeriod,LocalPointOfDelivery
FROM PLC_OFacts;

LCostBuckets:
Load [Point of Delivery];
sql execute L_CostBucketsQlikView;

//Point of Delivery DIM
DIMPointofDelivery:
LOAD LocalPointOfDelivery,
LocalPointOfDelivery as [Point of Delivery],
LocalPointOfDeliveryDescription AS PointofDeliveryDesc;
SQL SELECT * From L_LocalPointsOfDelivery;

Smita

Re: Multiple fact tables

Thu, 20 Mar 2014 08:49:22 GMT

thats great and that seems to have done the trick. So in this example the idea is to have in the dim tables another field which will link to the LCostBuckets?

Re: Multiple fact tables

Anonymous — Thu, 20 Mar 2014 09:06:25 GMT

It depends on your requirement mainly, if you want to filter DIMPointofDelivery table with LCostBuckets table and do not want the link between the LCostBuckets and LCFacts table, then it can be done in this way.

Re: Multiple fact tables

Thu, 20 Mar 2014 09:09:48 GMT

so my requirement here is that the DIMPOintofDelivery will be used against both LCostBuckets and LCFacts but i dont want the LCostBuckets and LCFacts to be linked (both the later are fact tables).

Re: Multiple fact tables

Anonymous — Thu, 20 Mar 2014 09:26:46 GMT

For multiple facts table, you can go with either concatenation or Link table concept. Above example of DIMPointofDelivery table is like a link table between the fact tables. As per your requirement, it should work fine.

However you can concatenate the fact tables, as it simply append the rows of tables into one without any link.

Re: Multiple fact tables

Thu, 20 Mar 2014 11:52:53 GMT

okay thanks.

So the approach i have taken in the example is fine then in that case.

Re: Multiple fact tables

Thu, 20 Mar 2014 12:27:13 GMT

What i am finding is when i add that LCostBuckets and reload the results (numbers are different). I would not expect adding this to have an impact on the sheet which is based on the other fact tables which are not linked to LCCostBuckets?

Re: Multiple fact tables

Thu, 20 Mar 2014 13:38:46 GMT

Re: Multiple fact tables

Thu, 20 Mar 2014 13:39:51 GMT

I have included the table view. You will notice that it is causing a loop when i start to add more fields to the LCostBuckets.

Please can you help?

Re: Multiple fact tables

Anonymous — Thu, 20 Mar 2014 13:55:08 GMT

if you want to keep number of rows as per your fact table PLCFacts, then you can do Left Keep or Left Join like as below example.

PLCFacts:

select * from PLCFacts;

DimPointofDelivery:

left keep(PLCFacts)

select * from DimPointofDelivery;

LCCostBuckets:

left keep(DimPointofDelivery)

select * from LCCostBuckets;

Regarding circular loop, rename any of those field which is not relevent to your requirement. In your scenario, if you need to still add more fields in LCCostBuckets table, then go with concatenation to main fact table. It would not change your dataset, it would simply append the data in one table and you can avoid synthetic key, circular reference in one go.

Re: Multiple fact tables

Thu, 20 Mar 2014 14:03:55 GMT

okay i will try that.

Appreciate your help on this.

Re: Multiple fact tables

Fri, 21 Mar 2014 09:41:39 GMT

i have done this

DIMPointofDelivery:

left keep (PLCFacts)

LOAD LocalPointOfDelivery, LocalPointOfDelivery AS LocalPoD, LocalPointOfDeliveryDescription AS PointofDeliveryDesc;

SQL SELECT *

From PLC_LocalPointsOfDelivery;

PLCCostBuckets:

left keep (DIMPointofDelivery)

Load [Point of Delivery] AS LocalPoD, [Site] as LocalSitePLCCost;

sql execute PLC_CostBucketsQlikView;

However when i run the script I get a table not found error.

Re: Multiple fact tables

Anonymous — Fri, 21 Mar 2014 09:51:38 GMT

From this query DIMPointodDelivery table is getting join to PLCFact table, because of which table is not available to join further with any other table. Try to join DIMPointofDelivery and PLCCostBuckets first and then join with PLCFactTable. Something like below.

DIMPointofDelivery_temp:

LOAD LocalPointOfDelivery, LocalPointOfDelivery AS LocalPoD, LocalPointOfDeliveryDescription AS PointofDeliveryDesc;

SQL SELECT *

From PLC_LocalPointsOfDelivery;

PLCCostBuckets:

left keep (DIMPointofDelivery_temp)

Load [Point of Delivery] AS LocalPoD, [Site] as LocalSitePLCCost;

sql execute PLC_CostBucketsQlikView;

DIMPointofDelivery:
left keep (PLCFacts)
LOAD LocalPointOfDelivery,
     LocalPoD,
     PointofDeliveryDesc
     LocalSitePLCCost
Resident DIMPointofDelivery_temp;

Drop Table DIMPointofDelivery_temp;

Hope it will work.

Smita