Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
I am new to Qlik Sense - would appreciate any help or resources on this:
I have seven relational tables (tbl1, tabl2, etc), all linked on a specific filed (fieldX). There are one-to-many (or one-to-none) relationships between the tables. How do I write a COUNT expression that counts the number of records in a given table on "filedX" (to create a measure that can then be used in visualizations)?
Thanks!
You can simply use Count(Distinct FieldX) or Count(FieldX) according to your requirements.
Also, you can create Flags for each table.. Say
1 as T1 for Table1
and so on
and can now use
Count({<T1 = {1} >}Distinct FieldX)
This will give you distinct count of FieldX based on Table 1 only.
The same way you can use for other tables as well.
HTH.
Thanks, Manish.
I imagine what you have recommended probably works well. But the counts are not correct for these tables - I think this is probably a limitation of my own understanding of how relational data sets are handled by Qlik Sense.
For example, I know that table2 has fourth of the records of table1, yet when I use the count function as you have described it (with tag =1), I get the same counts for both tables (i.e. the count for table1). Same is true for all other tables.
Although I did not do this, my best guess is that the tables are getting joined on table1, thus resulting in table1 row counts, but not sure.
I would appreciate any thoughts on this...
Hari
If you are joining tables, then create these flags before joining those tables.