topic How to count matching values in 3 different fields in App Development

How to count matching values in 3 different fields

Anonymous — Wed, 22 Dec 2021 15:42:54 GMT

Hi,

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

Campaign1	Campaign2	Campaign3
A	B
C	A	D
A	C	B
D	A
B	A

Expecting result:

A=5

B=3

C=2

D=2

Thanks in advance,

Fredrik

Re: How to count matching values in 3 different fields

Nagaraju_KCS — Thu, 15 Mar 2018 17:28:45 GMT

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.

Re: How to count matching values in 3 different fields

Digvijay_Singh — Thu, 15 Mar 2018 18:13:09 GMT

Check this out-

Re: How to count matching values in 3 different fields

sunny_talwar — Thu, 15 Mar 2018 18:20:02 GMT

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;

Re: How to count matching values in 3 different fields

Anonymous — Thu, 15 Mar 2018 19:45:16 GMT

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.