How to count of mutiple columns as total in straight table

[shabarish0587]

 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

[StarinieriG]

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]

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]

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

[shabarish0587]

HI Starinieri,

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

Regards,

S.Shabarish

[StarinieriG · Accepted solution]

Hi,

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