topic Re: How to Count matching values in 2 different fields in QlikView

How to Count matching values in 2 different fields

Wed, 25 Nov 2020 16:16:04 GMT

Im fairly new to qlikview and im trying to do the following , in the below table there is 3 zeros in the picked column and 3 zeros in the BOH column, so there is 3 instances were picked = 0 and BOH = 0.

how do i create an expression where i can get the value 3 returned ( this is the 3 instances were 0 = 0.)

picked	BOH
1	1
0	0
0	0
0	0
5	4

i believe in excel the countifs function can be used.

thanking you in advance

Lynn

Re: How to Count matching values in 2 different fields

Anil_Babu_Samineni — Thu, 31 Aug 2017 04:06:19 GMT

Are you expecting this?

Count({<picked = {'0'}, BOH={'0'}>} Measure)

Count(If(picked=0 and BOH = 0,Measure))

Re: How to Count matching values in 2 different fields

Thu, 31 Aug 2017 06:50:28 GMT

im trying to determine where ever picked = 0 and BOH = 0, how many instances of that is there.

Re: How to Count matching values in 2 different fields

sam_grounds — Thu, 31 Aug 2017 09:31:16 GMT

Hi Lynn,

This should do the trick. You just need to compare the two fields against each other to find matches...

=num(count(if(picked=BOH,[YourMainIdentifierField])),'#,##0')

If you're only matching the 0's, then this will suffice...

=num(count({$<picked={0},BOH={0}>}[YourMainIdentifierField])),'#,##0')

Sam

Re: How to Count matching values in 2 different fields

sathishbabu_s_g — Thu, 31 Aug 2017 10:01:12 GMT

Hi Lynn,

Just create a column like below. Let me know if it helps.

LOAD

Picked,

BOH,

if(Picked = BOH, 'TRUE', 'FALSE') as Match

INLINE [

Picked, BOH

1, 1

0, 0

5, 4

];

Re: How to Count matching values in 2 different fields

Anil_Babu_Samineni — Thu, 31 Aug 2017 13:21:05 GMT

May be?

Count({<picked = {'0'}, BOH={'0'}>} Instance) // Here, I haven't seen any Instance field in your data

Re: How to Count matching values in 2 different fields

sam_grounds — Thu, 31 Aug 2017 13:48:35 GMT

=num(count({$<picked={0},BOH={0}>}picked)),'#,##0')

Re: How to Count matching values in 2 different fields

Kushal_Chawda — Thu, 31 Aug 2017 13:56:41 GMT

Please provide enough data to build the logic, here you need to find out instances , so what should be counted as instances, is there any Key field in your data?

Re: How to Count matching values in 2 different fields

sam_grounds — Thu, 31 Aug 2017 14:53:35 GMT

You could just count one of the fields mentioned as outlined in my example above.