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.

[#2]

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

[Anonymous]

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.

[#4]

okay thanks.

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

[#5]

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?

[#6]

[#7]

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? 

[Anonymous]

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.

[#9]

okay i will try that.

Appreciate your help on this. 

[#10]

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.

[Anonymous]

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