topic Re: getting wrong count result when using key field in QlikView

getting wrong count result when using key field

surajap123 — Fri, 02 May 2014 11:07:05 GMT

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?

Re: getting wrong count result when using key field

lukaspuschner — Fri, 02 May 2014 11:09:05 GMT

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

load

key,

key as test

.....

Re: getting wrong count result when using key field

sushil353 — Fri, 02 May 2014 11:10:15 GMT

Hi Suraj,

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

For more understanding share you expression

Re: getting wrong count result when using key field

sudeepkm — Fri, 02 May 2014 11:14:05 GMT

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.

Re: getting wrong count result when using key field

Fri, 02 May 2014 11:16:58 GMT

Use Count(Distinct Key)

Re: getting wrong count result when using key field

surajap123 — Fri, 02 May 2014 11:22:31 GMT

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?

Re: getting wrong count result when using key field

hic — Fri, 02 May 2014 11:24:13 GMT

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

Re: getting wrong count result when using key field

surajap123 — Fri, 02 May 2014 11:32:41 GMT

Hi Henric,

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

Re: getting wrong count result when using key field

lukaspuschner — Fri, 02 May 2014 11:33:54 GMT

i think so

Re: getting wrong count result when using key field

hic — Fri, 02 May 2014 11:42:34 GMT

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

Re: getting wrong count result when using key field

surajap123 — Fri, 02 May 2014 12:18:28 GMT

Thanks for all the inputs..