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?
You are correct that Set Analysis is the way ahead. You will need to ignore any selections in TransactionChannelDesc for any expression where that value does not exist, eg:
sum({<TransactionChannelDesc=>}MyValue)
Now, if you've concatenated two tables and there are only some fields that are common, you will need to ignore a bunch of fields on any calculation - ie. the ones from the other table. Suggest you create an ignore list for table 1 and for table 2, e.g. in your load script add:
let vIgnoreTable1 = 'Field1=,Field2=,Field3=';
let vIgnoreTable2 = 'FieldA=,FieldB=,FieldC=';
You can then use these in each expression:
sum({<$(vIgnoreTable1)>}MyValue)
This way if you bring in an extra field to either side of the concatenation you can just add it to the ignore list and not have to modify all of your expressions.
This should work in charts and text boxes. In the text box in your example you need an aggregation around your field to make it always show a value, eg. max([Ly Date Key])
Looking further at your app, you may be better off with a table associated on an concatenated key via a link table, rather than a JOIN or a CONCATENATE.
To do this, create an extra field in both QVD's, like this:
date(date#([Date Id],'YYYYMMDD')) & ':' & [Loc #] as DateLocKey,
When loading into the front end, only load the DateLocKey in for each table (not the two fields that make it up). Then create a link table like this:
LinkTable:
LOAD DISTINCT
DateLocKey,
[Date ID] ,
[Loc #]
FROM C:\QlikView\Data\QVD\Transform\Sales_LocWk.qvd (Qvd);
LinkTable:
LOAD DISTINCT
DateLocKey,
[Date ID] ,
[Loc #]
FROM C:\QlikView\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);
There are pro's and con's of both approaches. Concatenate works best when you have many fields that are common between two tables, but when there are only two common fields the link table is good.
The way that selections work on the fields that are not in the link table differ between the two approaches, so you should make sure you understand what happens when you make selections.
