Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

fredalmq
New Contributor

How to count matching values in 3 different fields

Hi,

How do i create an expression where i can calculate how many of the same value it is in the different fields?

  

Campaign1Campaign2Campaign3
B
CAD
ACB
DA
BA

Expecting result:

A=5

B=3

C=2

D=2

Thanks in advance,

Fredrik

Tags (1)
4 Replies
qv_testing
Valued Contributor

Re: How to count matching values in 3 different fields

Try this.. !

Test:

LOAD * Inline [

Campaign1,Campaign2,Campaign3

A,B

C,A,D

A,C,B

D,A

B,A

];

Test1:

NoConcatenate

Load  Campaign3 as key Resident Test;

Load  Campaign2 as key Resident Test;

Load  Campaign1 as key Resident Test;

DROP Table Test;

I feel this is not correct way, but one option.

Digvijay_Singh
Honored Contributor III

Re: How to count matching values in 3 different fields

Check this  out-

MVP
MVP

Re: How to count matching values in 3 different fields

Another option is to use CrossTable load


Temp:

CrossTable (Campaign, Value)

LOAD 1 as 1,

*;

LOAD * INLINE [

    Campaign1, Campaign2, Campaign3

    A, B

    C, A, D

    A, C, B

    D, A

    B, A

];


FinalTable:

LOAD Campaign,

Value

Resident Temp

Where Len(Trim(Value)) > 0;


DROP Table Temp;

fredalmq
New Contributor

Re: How to count matching values in 3 different fields

Hi you,

Thanks for the answer's!

Two things,

I'm using Qlik Sense, how to open the .qvw files?

I having my data in an Excel sheet and the tab is called Data. The example I add was only some example how it looks like but in my file I have more then 1000 row with data. So not only the row I add in my question.