counting instances of same value across different columns

[Sanghamitra]

hi, I am new to qlik and still figuring out the syntax and methods.

I have a table with columns C, R, and A all of which have string type data.

I want to count the number of times the entry in R and A are the same value distinct for each entry.

Would really appreciate the help.

Thanks.

[Angela_Zhou]

@Sanghamitra 

Can you give some sample data in column R and A with expected final count result?
The solutions are also different when you try to achieve it in a data table in backend loading script, or in front end UI table/KPI.  Please clarify.

[Sanghamitra]

hi,

both columns contains names of people

I want to show the number of times a person has entries in both columns. and I want to achieve it in front end table/kpi.

sample

R                              A

abhi                      nandini

nandini              nandini

shekhar             asif

etc                       etc

in the full dataset the name nandini appears 14 times (i.e. 14 rows have the same corresponding values).

for the above sample it would be 1 for nandini.

[Angela_Zhou · Accepted solution]

@Sanghamitra There are two ways to achieve it.

1) Adding extra count field "isSame" in loading script to mark 1 if matching found, otherwise zero. Then later you can simply sum up this new field in Table or API, like below:

2) Without introduce new field, you have to use Aggr() function, like

In table:

In KPI,

Sum(

    Aggr(

        sum(if(A=R, 1, 0))

   ,A,R)

)

[Sanghamitra]

It works.  Thank you.😄