6 Replies Latest reply: Sep 14, 2011 1:37 PM by chris3110

# How do joins work in QlikView?

Hi there,

I posted a question yesterday on "New to QlikView" however I haven't really received a satisfying answer yet; also my initial message was somewhat confusing so I'll repost it here in a more clear form. I'm using QlikView 10.00.9061.7 SR3 on Windows 7 32-bit.

My script looks like this:

```flight:
day, fligh_id, aircraft
mon, 1, 737
mon, 2, 320
mon, 3, 737
];

aircraft:
aircraft, seats
737, 500
320, 150
];
```

And the results I get are as follows:

My questions are:

1. Why is QlikView computing the last column as a sum(DISTINCT seats) when I didn't ask for a DISTINCT one?
2. What should I do in order to get the intuitive (and more correct imo) result sum(total <day> seats) = 1150 ?

Thanks to all for your help,

Chris

• ###### Re: How do joins work in QlikView?

Hi Chris,

Go to the chart properties, and in the Expressions tab, click con "Sum(seats)" and select Total Mode "Sum Of Rows". The field "seats" has two values, and Sum(seats) will return the sum of the two values (650). In the second expression you can use

```RangeSum(Top(Sum(seats), 1, NoOfRows()))
```

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### How do joins work in QlikView?

conveert your pivot chart to Straight chart and

go to expression-> select sum of rows

and then convert again into pivot chart you get correct result

do for both expression

i will get back to you for second expression

• ###### How do joins work in QlikView?

see the miguel reply for second expression

thanks you miguel

• ###### Re: How do joins work in QlikView?

Hi Chris

flight:

day, fligh_id, aircraft

mon, 1, 737

mon, 2, 320

mon, 3, 737

];

left join (flight)

aircraft, seats

737, 500

320, 150

];

hth/gg

• ###### Re: How do joins work in QlikView?

Yep, this is probably the best option, creating new fields in each record with the total amount.

The reason is that QlikView doesn't join data, but associate it. That's why you can specifically join resulting tables, use mapping tables, and so.

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: How do joins work in QlikView?

Hi guys,

thanks for all the answers. I think Gandalf has it right, in order to get the correct results what I must do is really use a join and not rely on QlikView's automatic association of identically-named columns.

The best explanation I've been able to found about that issue is on this page. Basically what happens is that column associations are not SQL joins, they are simply indications that the columns point to the same object in memory (in my case the "aircraft" object being pointed to by two different columns).

So all tables in a model that are part of a "fact" table, i.e., containing measure columns, should really be joined using true SQL joins as pointed by Gandalf, which will create one single fact table containing all measures, While "automatic" associations are ok for dimension values that will only be used for filtering and drill-down