topic How do joins work in QlikView? in QlikView

How do joins work in QlikView?

Fri, 09 Sep 2011 10:32:22 GMT

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:
LOAD * INLINE [
   day, fligh_id, aircraft
    mon, 1, 737
    mon, 2, 320
    mon, 3, 737
];

aircraft:
load * inline [
 aircraft, seats
  737, 500
  320, 150
];

And the results I get are as follows:

My questions are:

Why is QlikView computing the last column as a sum(DISTINCT seats) when I didn't ask for a DISTINCT one?
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?

Miguel_Angel_Baeyens — Fri, 09 Sep 2011 10:49:08 GMT

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?

SunilChauhan — Fri, 09 Sep 2011 10:59:56 GMT

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?

SunilChauhan — Fri, 09 Sep 2011 11:01:48 GMT

see the miguel reply for second expression

thanks you miguel

Re: How do joins work in QlikView?

gandalfgray — Fri, 09 Sep 2011 11:39:21 GMT

Hi Chris

Do a join instead in your script:

flight:

LOAD * INLINE [

day, fligh_id, aircraft

mon, 1, 737

mon, 2, 320

mon, 3, 737

];

left join (flight)

load * inline [

aircraft, seats

737, 500

320, 150

];

hth/gg

Re: How do joins work in QlikView?

Miguel_Angel_Baeyens — Fri, 09 Sep 2011 11:42:32 GMT

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?

Wed, 14 Sep 2011 17:37:54 GMT

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

Thanks for your help,

Chris