Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
surajap123
Creator III
Creator III

getting wrong count result when using key field

Hi All,

I am geting wrong count value when i use the key field in the expression.

To give the background information, the key field is linking to multiple tables in my data model. Like the picture below.

linkkk.png

I am thinking to create a link table. Will this solve the problem?

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Depends on what you want to count.

First of all, if the data model is OK, you should not change the name of any key field. What you could do, is to make a copy of the key in the table where you want to count the records, and have an alias for this field.

But since it is a key field, I assume that you want to count the number of distinct values? Or? And then you should use Count(distinct ...) to get the correct number of Bookings.

(I hope you are not avoiding Count(distinct) because of performance reasons? If so, read this: http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/22/a-myth-about-countdistinct)

HIC

View solution in original post

10 Replies
lukaspuschner
Partner - Creator
Partner - Creator

counting keyfields is not a good idea, load the keyfield twice and give an Aliasname and cout this one.

load

key,

key as test

.....

sushil353
Master II
Master II

Hi Suraj,

Is the key field is unique in nature.? are you doing distinct count?  any set analysis ?

For more understanding share you expression

sudeepkm
Specialist III
Specialist III

Have you checked the information density and subset ratio of the key field across all the tables.

It may happen that after making the association few values might have added from other tables.

Not applicable

Use Count(Distinct Key)

surajap123
Creator III
Creator III
Author

Hi Sushil,

I am using the below expression. When i aliase the 'booking' key field it is giving the right count result.

=Count({<TransactionType={'Registration'}>} Booking)

Since this key field is used in multiple tables, Do i need to aliase this field in every table, whenever i am using filter field from that table in set analysis?

@Sudeep- The information is 100% in all tables for that field, but subset ratio is less ie 98%, 99.97% etc in few tables(where this key field is present). What shall i do to the key field when subset ratio is less in that table?

hic
Former Employee
Former Employee

The Count(<field>) function counts the number of records in the table where <field> exists (and <field> isn't NULL). But if you use a key field as parameter, the above definition is ambiguous - the field exists in more than one table.

Most likely you want to count the number of field values, and then you should use Count(distinct ...)

HIC

surajap123
Creator III
Creator III
Author

Hi Henric,

Since my key field is present in multiple tables, so do i need to aliase this field in every table?

lukaspuschner
Partner - Creator
Partner - Creator

i think so

hic
Former Employee
Former Employee

Depends on what you want to count.

First of all, if the data model is OK, you should not change the name of any key field. What you could do, is to make a copy of the key in the table where you want to count the records, and have an alias for this field.

But since it is a key field, I assume that you want to count the number of distinct values? Or? And then you should use Count(distinct ...) to get the correct number of Bookings.

(I hope you are not avoiding Count(distinct) because of performance reasons? If so, read this: http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/22/a-myth-about-countdistinct)

HIC