How to join multiple facts into a single table

[#1]

Hi,

I have multiple fact tables coming from multiple QVW's and in my application i want to have a centralised fact where we have only 1 fact and multiple dimensions.

If i do a outer join or inner join or even concatenate those 2 facts then the values i get for the lets say sales are huge which looks like they are cross joined.

SO, Can someone please help me with any suggestions on how to join multiple facts into a single fact table?

Thanks

[maxgro]

if you want 1 fact table from multiple tables maybe you need a concatenate

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

[#3]

Hi Grossi,

Looks like we can use concatenate when we have 2 fact tables with same columns,how about if i have 2 fact tables with just one same column and remaining all different columns?

Thanks

[Anonymous]

Look into Link Tables.

[maxgro]

you can concatenate tables also when there isn't common fields

a:

load * inline [

field1, field2

a, 1

aa, 11

];

concatenate (a)

load * inline [

f3, f4

a, 1

aa, 11

];

[#6]

Hi Massimo,

I tried concatenate but as i mentioned earlier that if i do concatenate then the values of sales are showing up huge instead of their original values, May be if i attach my QVW does that help?

Thanks

[Anonymous]

Hi,

Use the Left Join

[maxgro]

yes, if you can

[stevedark]

A concatenate will never cross join as it is not joining at all.

Are you sure that you don't have fields from both source tables going into the same field in the concatenated table.  This would certainly give you larger numbers than you are expecting.

You can use the AS statement to ensure that you get two separate columns in your concatenated table, rather than one column with too many numbers.

Also check table viewer, if the concatenate didn't happen you will have two tables in the data model and no join between them.  That will give you a Cartesian join if you use fields from both tables in a single chart.  This will cause inflated numbers - and all kinds of other problems too.

Hope that helps.

Steve

[#10]

Hi Massimo,

attched is my QVW, the values look fine now, however if i select TransactionChannelDesc as POS then the traffic goes blank?I know its because that traffic doesnt have a channel to it?But is there any way i can do the traffic such that it gives me the values even if i select POS?i know i can use set analysis in my code but for the textbox?

Please let me know if you have any questions or if i am unclear.

Thanks.