How to get distinct record number from a table

[#1]

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

[Nicole-Smith]

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');

[#3]

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

[Nicole-Smith]

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

[Gysbert_Wassenaar]

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)