topic Re: How to join multiple facts into a single table in QlikView

How to join multiple facts into a single table

Thu, 24 Jul 2014 00:56:13 GMT

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

Re: How to join multiple facts into a single table

maxgro — Thu, 24 Jul 2014 01:53:40 GMT

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

Re: How to join multiple facts into a single table

Thu, 24 Jul 2014 01:57:36 GMT

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

Re: How to join multiple facts into a single table

Anonymous — Thu, 24 Jul 2014 02:05:59 GMT

Look into Link Tables.

Re: How to join multiple facts into a single table

maxgro — Thu, 24 Jul 2014 02:08:26 GMT

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

load * inline [

field1, field2

a, 1

aa, 11

];

concatenate (a)

load * inline [

f3, f4

a, 1

aa, 11

];

Re: How to join multiple facts into a single table

Thu, 24 Jul 2014 02:30:03 GMT

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

Re: How to join multiple facts into a single table

Anonymous — Thu, 24 Jul 2014 03:49:02 GMT

Hi,

Use the Left Join

Re: How to join multiple facts into a single table

maxgro — Thu, 24 Jul 2014 07:56:52 GMT

yes, if you can

Re: How to join multiple facts into a single table

stevedark — Thu, 24 Jul 2014 08:01:38 GMT

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

Re: Re: How to join multiple facts into a single table

Thu, 24 Jul 2014 13:46:20 GMT

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.

Re: Re: How to join multiple facts into a single table

stevedark — Thu, 24 Jul 2014 14:18:56 GMT

Hi there,

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

Hope that all makes sense.

Steve

Re: Re: How to join multiple facts into a single table

stevedark — Thu, 24 Jul 2014 14:32:11 GMT

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.

Steve