How do joins work in QlikView?

[#1]

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:

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

[Miguel_Angel_Baeyens]

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

[SunilChauhan]

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

[SunilChauhan]

see the miguel reply for second expression

thanks you miguel

[gandalfgray · Accepted solution]

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

[Miguel_Angel_Baeyens]

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

[#7]

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