topic Re: How to use Match in Nested IFs in QlikView

How to use Match in Nested IFs

Wed, 25 Nov 2020 16:16:04 GMT

Hello -

I need help with the below IF statement. I am trying to use MATCH to look at multiple vales in a field.This is not working.

Is MATCH the correct function to use in this case? Can someone please suggest the right way to go about this.

Thank You.

*******************************************************************************************************

IF(Job='0','Mng Dir',

IF(Job='9' AND Level = '8', 'Mng Dir',

IF(Job='9' AND Level = '10', 'Dir',

IF(Job='9' AND Level = '11', 'Dir',

IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444'), 'SrMgr',

IF(Job='2' AND NOT MATCH(Jobcode,'11111', '22222','33333',44444'), 'Dir',

'No')))))) as "NeedAgreement"

*******************************************************************************************************

Regards,

Archana

Re: How to use Match in Nested IFs

craigsutton — Fri, 31 Mar 2017 19:26:45 GMT

Match returns 0 if no match and 1,2,3,4 in this case depending on which jobcode matched.

Try:

IF(Job='0','Mng Dir',

IF(Job='9' AND Level = '8', 'Mng Dir',

IF(Job='9' AND Level = '10', 'Dir',

IF(Job='9' AND Level = '11', 'Dir',

IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444')>0, 'SrMgr',

IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444')=0, 'Dir',

'No')))))) as "NeedAgreement"

Re: How to use Match in Nested IFs

hector_munoz — Fri, 31 Mar 2017 19:27:50 GMT

Hi Archana,

I think using Match() function there is a very smart option but Match() function returnna a number.

E.g. Match(vdOne, 'One', 'Two', 'Three') returns 1 as variable vdOne containe 'One' value. So in your case you should put:

IF(Job='0','Mng Dir',

IF(Job='9' AND Level = '8', 'Mng Dir',

IF(Job='9' AND Level = '10', 'Dir',

IF(Job='9' AND Level = '11', 'Dir',

IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444') > 0, 'SrMgr',

IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444') = 0, 'Dir',

'No')))))) as "NeedAgreement"

Hope it serves,

Regards,

Re: How to use Match in Nested IFs

selcukcadir — Fri, 31 Mar 2017 19:30:07 GMT

=IF(Job='0','Mng Dir',

IF(Job='9' AND Level = '8', 'Mng Dir',

IF(Job='9' AND Level = '10', 'Dir',

IF(Job='9' AND Level = '11', 'Dir',

IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333','44444' ) > 0, 'SrMgr',

IF(Job='2' and MATCH(Jobcode,'11111', '22222','33333','44444') = 0, 'Dir',

'No'))))))

Re: How to use Match in Nested IFs

Fri, 31 Mar 2017 19:40:24 GMT

Hector - This works.

Thank you Thank you Thank you!!

Re: How to use Match in Nested IFs

Fri, 31 Mar 2017 19:41:06 GMT

Thank You Cadir