Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shabarish0587
Contributor
Contributor

How to count of mutiple columns as total in straight table

 Hi Friends,

 

            I am facing one issue like i have three columns . i want to count these three columns in one expression. normally we can use count(distinct column 1)+count(distinct column 2 ) like this.  but here in column one and column two, we have repeated value in any one column it should not count in our expression. i am sharing sample file. in excel file  if you observe we have IDs like   1 and 3. if we calculate count of TKT_ID+PT_ID+ CT_ID.  for ID 1 it should come total is 7. but i am getting 9, like this for ID 3 is also total is 10 but i am getting 12.  can any one help me on this. Thanks in advance

 

1 Solution

Accepted Solutions
StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

 

If you have 3 different tables with different values, you could try something like this (attached).

View solution in original post

5 Replies
StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

you could solve that creating a new field in this way:

NoConcatenate
TEST:
LOAD
ID,
TKT_ID AS FIELD
Resident TAB;

Concatenate (TEST)
LOAD
ID,
PT_ID AS FIELD
Resident TAB;

Concatenate (TEST)
LOAD
ID,
CT_ID AS FIELD
Resident TAB;

 

And you could use the expression count(distinct FIELD)

 

shabarish0587
Contributor
Contributor
Author

Hi Starinieri,

             Thanks for your Reply. I have tried your approach.But here i have a question, in my data model we have multiple tables with same columns so we used link table for this. when i tried with your approach with one table i am getting distinct value but some cases i am not getting exact value because some values are coming from another tables. So do you have any another way by using set analysis at UI level.

StarinieriG
Partner - Specialist
Partner - Specialist

Have you tried to use ID & TableName AS KEY instead of ID? 
So you keep values of each table separated.

shabarish0587
Contributor
Contributor
Author

HI Starinieri,

 

                  Yes i have used ID as Key. Can you please elaborate what i want to do here.

Regards,

S.Shabarish

StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

 

If you have 3 different tables with different values, you could try something like this (attached).