Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Concatenate Many to Many Fact Tables

Hi

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

1 Solution

Accepted Solutions

Re: Concatenate Many to Many Fact Tables

You can concatenate like this

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

9 Replies
MVP
MVP

Re: Concatenate Many to Many Fact Tables

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

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

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+

avkeep01
Valued Contributor

Re: Concatenate Many to Many Fact Tables

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

I think you create the link table like this

Re: Concatenate Many to Many Fact Tables

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

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.


talk is cheap, supply exceeds demand

Re: Concatenate Many to Many Fact Tables

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

Re: Concatenate Many to Many Fact Tables

Your concat suggestion looks good.

Community Browser