Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Thanks to all for your help,
Chris
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
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.
BI Consultant
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
see the miguel reply for second expression
thanks you miguel
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
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,
BI Consultant
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