3 Replies Latest reply: Mar 15, 2018 3:15 PM by Manish Kachhia RSS

    Count in Relational Tables

    Hari Sin

      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!

        • Re: Count in Relational Tables
          Manish Kachhia

          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.

            • Re: Count in Relational Tables
              Hari Sin

              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