Newbie Question about table links

[#1]

Dear Forum,

I have just starting in Qlikview, and my company have chosen this product for our information mining solution. I don't quite understand about the linking, and would some clarification.

I have 2 tables, Cars and Passengers. 1 car has many passengers. I want to create reports such as 'Car make vrs Car Count' and 'Oldest driver in Car' as well as more complex reports such as 'Most Popular car make for transporting children'

If I do a pivot table object with 'Car make' as the dimension and Count(CarID) as the expression, I get the wrong answer because it is counting each passenger, and I can cope with that by changing my expression to Count({<PassengerType={"Driver"}>}CarID).

Is there any easier way?

Thanks in advance,

NHS Bedfordshire

[kji]

Count(DISTINCT CarID) is the preferred way to do this.

[#3]

Thank you for the quick responce, that certainly works. What about displaing the oldest pasenger in the car?

[johnw · Accepted solution]

To expand a bit on the count(distinct CarID), you would always want to use "distinct" when counting a field that links two tables together. Otherwise, QlikView isn't sure which table you mean, and you can actually get "unpredictable" results, or results that differ between different QlikView versions. So if it's a linking ID, use "distinct", or even create a counter field on the table you care about with a value of 1. You can then either sum(CarCount) or count(CarCount), and brief testing seems to show one or the other being faster in different versions of QlikView. Not sure how it compares to a count(distinct CarID).

oldest passenger in each car: CarID as dimension, max("Passenger Age") as expression

most popular car make for transporting children: Honestly, unless I had very pushy users that insisted on it, I wouldn't do this directly. To me, "for transporting children" is something the user should select on the fly, not something I should "hard code" into the report. QlikView is supposed to be a dynamic environment, after all.

I would probably set up a "most popular car" table, with "Car Make" as the dimension, and count(distinct CarID) as the expression, and sort descending by Y value. Have the USER select the passenger ages of interest. If they want to define children as anyone 12 or under, they select all ages up to 12, and the "most popular car" table will then be showing the most popular cars used to transport children up to age 12.

[#5]

Thanks you for your help.