topic Re: How to get distinct record number from a table in QlikView

How to get distinct record number from a table

Fri, 01 Nov 2013 18:41:11 GMT

Hi,

This is the first time I add multiple tables into Qlikview. I used to merge all the data into one query by all kinds of joins. My questions is how to get distinct number of record from one of the tables. Thanks

Longmatch

Re: How to get distinct record number from a table

Nicole-Smith — Fri, 01 Nov 2013 19:01:43 GMT

Pass whatever field that makes your record distinct into:

fieldvaluecount(fieldname)

Returns the number of distinct values in a field. Fieldname must be given as a string (e.g. a quoted literal).

Example:

let x = fieldvaluecount('Alfa');

Re: How to get distinct record number from a table

Fri, 01 Nov 2013 19:21:22 GMT

I have two tables in my app with same field of hospitalaccountID - table1 and table2. There are multiple records for a single hospital account id in table 2. I want to count unique hospitalaccountid in table2. I think the two tables will be joined by common data field - hospitalaccountid. Can we access data field by table name or is there any other way around? Thanks

Re: How to get distinct record number from a table

Nicole-Smith — Fri, 01 Nov 2013 19:34:55 GMT

If you load table2 in first in your load script, then use

let x = fieldvaluecount('hospitalaccountID');

you will store the number of distinct hospitalaccountID in variable x

Re: How to get distinct record number from a table

Gysbert_Wassenaar — Sat, 02 Nov 2013 10:02:13 GMT

If you have another field in the same table that doesn't contain null values you can use that in an expression like this:

count({<OtherFieldInSameTable={'*'}>} distinct hospitalaccountID)