Grouping records by a field from another table

[paulberendts]

I'm having trouble getting results grouped by the right field.

I have two tables, transports and cars:

Transports:
Date Persons  Car
1-1-2013 2 1
1-2-2013 3 1
1-3-2013 2 1
1-4-2013 4 1
1-5-2013 1 1
1-1-2013 4 2
1-2-2013 3 2
1-3-2013 1 2
1-4-2013 4 2
1-5-2013 5 99

Cars:
Car Engine
1 Euro5
2 Euro4

See that Car 99 is not in the cars file.
What I want to calculate is the number of transport lines that are made with a Euro5, Euro4 and Euro3 engine:
Cars which are not in the Cars file should be assumed to have an Euro5 engine:

Engine Transports%
Euro5 60%
Euro4 40%

I try to calculate this with a Grafiek (Chart?) with no dimensions, and an axpression:
100*sum(if(Engine='Euro5',1,0))/count(Engine)

This yields
Engine Transports%
Euro5 50%
Euro4 50%

Ia apparantly get only a line back for each line in Cars.

Can anybody explain to me how to get the results I need?

[Jason_Michaelides]

I think you have run into the classic QlikView challenges of dealing with NULLs. If you keep Transports and Cars as separate tables, then there cannot possibly be a link between them therefore it becomes extremely difficult to build an association with expressions.

I can see 2 solutions with the limited information provided:

1. Add extra records to the Cars table for all Cars that exist in the Transports table.
2. Join the Transports and Cars tables together and replace NULLs with your desired values.

Option two is probably easier.  It is done entirely in the script:

//Create a single table for Transports and cars
TransportsAndCars:
LOAD * FROM Transports...;
//Natural join to ensure you get all cars and all transports
JOIN (TransportsAndCars)
LOAD * FROM Cars...;
//Create the map to use to replace NULL values
Map_Nulls_Engines:
LOAD
     Null()
     ,'Euro5'
Autogenerate 1;
MAP Engine USING Map_Nulls_Engines;
//In order for the map to apply, you have to load the table again. You don't need this bit if you are performing a RESIDENT load with TransportsAndCars later in the script.
RIGHT JOIN (TransportsAndCars) LOAD DISTINCT * RESIDENT TransportsAndCars;

Now you have created the required Euro5 values in the data itself, the expressio0ns can remain simple.

Hope this helps,

Jason

[paulberendts]

Thanks for the input.

Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

Using a when statement for the Engine field:

when (engine is null) then 'Euro5 else engine as Engine2

I could enter a default value for the cars that did not have a record in Cars.

This gave me exactly the results I needed.

Thanks.

[paulberendts]

Thanks for the input.

Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

Using a when statement for the Engine field:

when (engine is null) then 'Euro5 else engine as Engine2

I could enter a default value for the cars that did not have a record in Cars.

This gave me exactly the results I needed.

Thanks.

[paulberendts]

Thanks for the input.

Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

Using a when statement for the Engine field:

when (engine is null) then 'Euro5 else engine as Engine2

I could enter a default value for the cars that did not have a record in Cars.

This gave me exactly the results I needed.

Thanks.

[paulberendts]

Thanks for the input.

Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

Using a when statement for the Engine field:

when (engine is null) then 'Euro5 else engine as Engine2

I could enter a default value for the cars that did not have a record in Cars.

This gave me exactly the results I needed.

Thanks.

[paulberendts]

Thanks for the input.

Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

Using a when statement for the Engine field:

when (engine is null) then 'Euro5 else engine as Engine2

I could enter a default value for the cars that did not have a record in Cars.

This gave me exactly the results I needed.

Thanks.

[Jason_Michaelides]

Of course by using a LEFT JOIN you will not get all cars...part of the power of QV is telling you what ISN'T as well as what IS. In this case, what cars are not being used.

Glad you sorted it though.

Jason