Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bhaskarsushant
New Contributor

Need to nested if condition replace with pick match function

I am working on table. I have table Like -

ApplicableReportedResult
YYYes
YNNot Applicable
NNNA

I have done this issues using the "Nested If" condition.

like -

If(Applicable='Y' and Reported='Y', 'Yes'

    ,if(Applicable='Y' and Reported='N', 'Not Applicable'

    ,If(Applicable='N' and Applicable='N', 'NA'))) as "Result"

but I want to do this using the "Pick and Match function" because there is two condition (Applicable and Reported). how it possible.

Please help.

6 Replies
gerry_hdm
Contributor II

Re: Need to nested if condition replace with pick match function

i dont understand your Problem

result not in " " Setting and you become this table !

myIF:
load * Inline [
Applicable, Reported, ResultSoll
Y, Y, Yes
Y ,N, Not Applicable
N, N, NA
]
;
EndeFile:
load *,
If(Applicable='Y' and Reported='Y', 'Yes'
,
if(Applicable='Y' and Reported='N', 'Not Applicable'
,
If(Applicable='N' and Applicable='N', 'NA'))) as Result
resident myIF;

drop Table myIF;

bhaskarsushant
New Contributor

Re: Need to nested if condition replace with pick match function

Hi Gerold Roser

my question is 

Result = "Yes",  if Applicable = "Y" and Reported = "Y"

Result = "Not Applicable" ,  If Applicable  ="Y" and Reported = "N"

Result  = "NA" , If Applicable  =  "N" and Reported = "N".

I have done this issues with Nested If Condition. but I want to do this issues with "Pick and Match" function.

gerry_hdm
Contributor II

Re: Need to nested if condition replace with pick match function

undergrinder
Valued Contributor II

Re: Need to nested if condition replace with pick match function

Hi Sushant,

If these are the only possible states, then try this expression:

=pick(match('N',Applicable,Reported)+1,'Yes','NA','Not Applicable')

It assumes when Applicable='N' then the Reported field is also 'N'

G.

undergrinder
Valued Contributor II

Re: Need to nested if condition replace with pick match function

and don't manage that situation, when one of the fields is null

Re: Need to nested if condition replace with pick match function

Hi,

another solution might be:

QlikCommunity_Thread_309934_Pic1.JPG

table1:

LOAD Applicable,

    Reported, 

    Pick(Match(Applicable&'/'&Reported,

          'N/N' , 'N/Y'       , 'Y/N'            , 'Y/Y'),

          'NA'  , 'undefined' , 'Not Applicable' , 'Yes'

        ) as Result

FROM [https://community.qlik.com/thread/309934] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Community Browser