topic Graph data from 2 tables, inner join type relationship in QlikView

Graph data from 2 tables, inner join type relationship

Tue, 18 May 2010 23:34:58 GMT

Hi,
I want to graph data from 2 tables, where my 1st table may not have a record in the 2nd table. I want to show all metrics using data from the 2nd table and if the 1st table has records, add those to the metric.

Bookings Table
ID From_Statn To_Station PAX
1 0 5 5
2 0 2 1
3 2 4 3

Capacity Table: (the "onBoard" includes the PAX from the bookings table.)
Station Capacity Onboard
0 100 6
1 100 6
2 100 8
3 100 12

I want to Bar chart the following having the "Station" on the X-Axis.
Stack Chart that shows Current onboard, broken down by onboard and boarding.

Boarding: Sum(PAX) at the station level
OnBoard: Onboard - sum(pax) group by Station

However, i do not have PAX Bookings getting on at Stop 1, so i dont get a bar for that since the link between the 2 tables is the Station.

O = Onboard
B=Boarindg

B O B
B O B
B O B
B O O
B O O
B O O

0 1 2 3 4
Station is X-Axis

Any help appreciated!

Graph data from 2 tables, inner join type relationship

Wed, 19 May 2010 01:56:22 GMT

QlikView's associative data model "joins" tables together based of fields that are identically named in both tables. If you rename From_Statn as Station, QlikView will see the same column in two tables and associate these tables based on Station.

I'm having difficulty attaching the QVW file, so here is the inline data used in the load script...

Bookings:
LOAD * INLINE [
 Id, Station, To_Station, PAX
 1, 0, 5, 5
 2, 0, 2, 1
 3, 2, 4, 3
];

Capacity:
LOAD * INLINE [
 Station, Capacity, OnBoard
 0, 100, 6
 1, 100, 6
 2, 100, 8
 3, 100, 12
];

Graph data from 2 tables, inner join type relationship

Thu, 24 Jun 2010 20:10:17 GMT

Thanks for answering my question.

I added another twist that is causing me issues.

I now have multiple dates for both transactions and capacity.

So each train departs on a particular date. The problem now is that

- I want to sum the capacity for each date to determine the total capacity, but since the table is linked to the transactions my sum() sums capacity * (# transactions).

What i want is for it to sum(transactions) from transaction table as metric 1 and metric 2 is sum(capacity) from capacity table.

Ideas?

Bernard