Multiple fact tables

[#1]

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.

[MK_QSL]

NoConcatenate

TABLEB:

LOAD A, B, C, D, F

SQL exec QLIKVIEW_SP

[#3]

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

[Anonymous]

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

[#5]

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

[Anonymous]

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

[#7]

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

[Anonymous]

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

[#9]

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?

[Anonymous]

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.