topic Using the Count function in a Table Box in QlikView

Using the Count function in a Table Box

Wed, 10 Jun 2009 06:52:53 GMT

I have a large number of records in my Qlikview table. One of the fields is Employee_ID. Under normal cricumstances there would be an unique employee id for each record. In some cases a single employee ID will appear in more than one record. I would like to display those particular records in list box.

Using the statement =Count(distinct(EMPLOYEE_ID))>1 in the Calculation Condition of the List Box does not work. I do not think I am using that option corectly.

Can someone please tell me how it can be done

Using the Count function in a Table Box

johnw — Wed, 10 Jun 2009 07:11:19 GMT

In the list box properties, general tab, select <Expression>, edit it, and put in this:

=aggr(if(count(EMPLOYEE_ID)>1,EMPLOYEE_ID),EMPLOYEE_ID)

There might be an easier way, but that should do the trick.

Using the Count function in a Table Box

Wed, 10 Jun 2009 08:24:37 GMT

The table box still shows records that have a Employee_ID that only appears in taht records. The filtering that is required is like Select * from table where Count(Employee_ID) > 1 Group By Employee_ID. Can Qlikview do this sort of filtering in the front end.

Using the Count function in a Table Box

johnw — Thu, 11 Jun 2009 00:07:38 GMT

Ah, I paid attention to the post itself rather than the title, so I gave you instructions for a list box like the post requested. For a table box, use the expression I gave you as a calculated dimension, then suppress when value is null.

Using the Count function in a Table Box

Thu, 11 Jun 2009 04:45:04 GMT

Instead of doing a Count(DISTINCT... could you just create a table where the dimension is Employee ID and the Expression is:

Count( [Employee ID])

and then sort this descending. This should give you a list of the number of times each Employee ID appears in the table. If, as you say, there should be a unique Employee ID for each record then most of the rows will have a count of 1 and only those that appear in multiple rows will have a count > 1.

Hope this helps,

sjprows

Using the Count function in a Table Box

Thu, 11 Jun 2009 04:51:38 GMT

To create a list box out of it you would go to, as John said, the general tab on the List Box properties and select <Expression>...then add the following expression:

=If(aggr(NODISTINCT Count(EMPLOYEE_ID), EMPLOYEE_ID)>1,EMPLOYEE_ID)

This will give you a list box with only those employees that appear more than once in your table

Regards

sjprows