getting wrong count result when using key field

[surajap123]

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.

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

[lukaspuschner]

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]

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]

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.

[#5]

Use Count(Distinct Key)

[surajap123]

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]

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]

Hi Henric,

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

[lukaspuschner]

i think so

[hic · Accepted solution]

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