topic Re: Concatenate Many to Many Fact Tables in QlikView

Concatenate Many to Many Fact Tables

Anonymous — Thu, 14 Jan 2016 09:34:08 GMT

I am sorting a data model that has evolved over time into something that now looks like the Flying Spaghetti Monster.

A lot of the Fact Tables have many to many relationships between them.

I would like to concatenate these Fact Tables but that seems easier said than done. Is this sensible or would Link tables be better ?

Many Thanks, Bill

Re: Concatenate Many to Many Fact Tables

tresB — Thu, 14 Jan 2016 09:44:02 GMT

Rule of thumb:

'If the data is big - opt for concatenate, else link table.'

By any chance if you have missed this: Concatenate vs Link Table

Re: Concatenate Many to Many Fact Tables

Kushal_Chawda — Thu, 14 Jan 2016 09:48:28 GMT

If this all fact table have relationship between them then it is better to create the link table.

Re: Concatenate Many to Many Fact Tables

Anonymous — Thu, 14 Jan 2016 12:27:35 GMT

Thanks for your inputs.

Attached are two sample qvw's.

199535Link.qvw

Has 2 Fact Tables and a Link Table
Front end objects all behave when Dimension values are selected in the List Boxes

199535Concat.qvw

Has a single Fact Table and a Link Table
But the front objects do not all behave when Dimension values are selected in the List Boxes
I cannot get it to function at all without a Link Table that has one more column than 199535Link.qvw

My knees jerk is to Concatenate, but I cannot get it to behave.

Any suggestions ?

The real data is few hundred million rows and shall soon grow to 1 billion+

Re: Concatenate Many to Many Fact Tables

avkeep01 — Thu, 14 Jan 2016 15:21:14 GMT

Hi bill.markham‌

After looking at your concat.qvw I was wondering why you made the LinkAB table. That is just a resident of the concat table which already has all data in it.

And you wrote that the dimensions do not behave in the concat .qvw. But i think that they don't behave right in the link table. For example:

When selecting XXX in the DimLinkOne both value A and value B are associated. Next i add the selection GGG in Dim Two. GGG isn't connect to value B at all. But in the link table it shows 300 as a sum of value B and in my opion that isn't correct.

Of course i don't know the business rules behind the data model. Maybe you can explain it a bit more for me?

Re: Concatenate Many to Many Fact Tables

Kushal_Chawda — Thu, 14 Jan 2016 16:46:08 GMT

I think you create the link table like this

Re: Concatenate Many to Many Fact Tables

Kushal_Chawda — Thu, 14 Jan 2016 17:12:42 GMT

You can concatenate like this

I think you can go for concatenation like this instead of link table as per the data you have

Re: Concatenate Many to Many Fact Tables

Gysbert_Wassenaar — Thu, 14 Jan 2016 17:31:29 GMT

It looks like you want to aggregate Value B and Count B values over Dim Two values. I think that means a join and thus a link table. So I don't think concatenate will work for that.

Re: Concatenate Many to Many Fact Tables

Anonymous — Thu, 14 Jan 2016 17:59:52 GMT

Your Link Table suggestion seems to result in some double counts in the front end.

Re: Concatenate Many to Many Fact Tables

Anonymous — Thu, 14 Jan 2016 18:00:44 GMT

Your concat suggestion looks good.