topic Re: How to count entries between 2 tables in QlikView

How to count entries between 2 tables

Wed, 02 Mar 2016 15:25:04 GMT

Hi all,

I'm relatively new to QlikView and I'm trying to build an application, with a datamodel that contains sales information.

Let's say that I have the following tables and associations:

Employee

----------------

EmpID

First name

Last name

...

Orders

---------------

OrderID

CustomerID

EmpID

Order date

Customers

----------------

CustomerID

ContactName

...

So Employee is associated with Orders, and orders is Associated with Customers. There is no direct association between Employee and Customers.

What I'd like to achieve now, is a field which contains the number of customers for a given employee. I can't figure out how to solve this. Should this be done in scripting? Where would be the best location for this field? I would assume in the Employee table, but how ? What are the possible solutions and which one would you recommend ?

Thanks for helping me out !

Re: How to count entries between 2 tables

Gysbert_Wassenaar — Wed, 02 Mar 2016 16:45:17 GMT

Just load the tables. Qlikview will associate the tables using the field names that tables have in common. You can then add a straight table object with EmpID, First name and Last name as dimensions and count(distinct CustomerID) as expression

Re: How to count entries between 2 tables

Wed, 02 Mar 2016 17:23:33 GMT

Thank you very much. This is definitly showing what I want as information, but I was also looking to how and if it's possible to create a field for that in the Employee's table.

My guess is that this is not really needed since I can easily get the number of customers as you described, but out of interest and to learn something, I'd like to know how to do that, because I am not really able to do that right now.

Thank you again.

Re: How to count entries between 2 tables

Gysbert_Wassenaar — Wed, 02 Mar 2016 18:34:11 GMT

You could join the tables in the script using the JOIN predicate to make on big table from the three original tables.

Re: How to count entries between 2 tables

maxgro — Wed, 02 Mar 2016 18:53:15 GMT

...

but out of interest and to learn something, I'd like to know how to do that,

....

you can read the pdf here

Joins and Lookups

or here

To Join or not to Join

Re: How to count entries between 2 tables

Wed, 02 Mar 2016 21:03:19 GMT

Thank you very much all for the information, exactly what I was looking for !