Issue with expression containing several "IFs"

[RubenMoreno]

Hello,

I have  loaded a table in Qlik Sense including two columns:

ColumnA  including names [Maria R, Sebastian L, Isabel S, etc]

ColumnB including the following 4 options [N, P, R, L]

I want to create ColumnC with the following criteria

If ColumnA equal to "Maria R" or "Sebastian L" or "Isabel S", then ColumnC should include the value "Team1"

If ColumnA not equal to "Maria R" or "Sebastian L" or "Isabel S" and ColumnB equal to N, then ColumnC should include the value "Team2"

If ColumnA not equal to "Maria R" or "Sebastian L" or "Isabel S" and ColumnB equal to P, then ColumnC should include the value "Team3"

If ColumnA not equal to "Maria R" or "Sebastian L" or "Isabel S" and ColumnB equal to R, then ColumnC should include the value "Team4"

If ColumnA not equal to "Maria R" or "Sebastian L" or "Isabel S" and ColumnB equal to L, then ColumnC should include the value "Team5"

Could you please help? I am trying to build an expression with "if" but it is not working.

Thank you.

Best regards,
Rubén

[Taoufiq_Zarra]

maye be :

Pick(if(match(ColumnA, 'Maria R','Sebastian L','Isabel S')>0,1,
if(( match(ColumnA, 'Maria R','Sebastian L','Isabel S')=0 and match(ColumnB, 'N')>0),2,
if(( match(ColumnA, 'Maria R','Sebastian L','Isabel S')=0 and match(ColumnB, 'P')>0),3,
if(( match(ColumnA, 'Maria R','Sebastian L','Isabel S')=0 and match(ColumnB, 'R')>0),4,
if(( match(ColumnA, 'Maria R','Sebastian L','Isabel S')=0 and match(ColumnB, 'L')>0),5
))))),'Team1','Team2','Team3','Team4','Team5') as ColumnC,

[RubenMoreno]

Hello @Taoufiq_Zarra ,

I tried to apply the formula above to my  problem and I get the following message

"The following error occurred:

Field 'BFE Responsible' not found"

[BFE Responsible] is what I called "ColumnA" in the example.

Please find below my code:

[Weekly Report]:
LOAD
[BFE Responsible],
[Prog Code],
Pick(if(match([BFE Responsible], 'OLIVIER','ALEXANDER')>0,1, if((match([BFE Responsible], 'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'N')>0),2, if((match([BFE Responsible], ''OLIVIER','ALEXANDER')=0 and match([Prog Code], 'P')>0),3, if((match([BFE Responsible],'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'R')>0),4, if((match([BFE Responsible],'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'L')>0),5 ))))),'Team1','Team2','Team3','Team4','Team5') as [Team]
FROM [lib://...]
(ooxml, embedded labels, table is [Weekly Report]);

Can you support? I am getting crazy...

Thanks you in advance.

Rubén M.

[Taoufiq_Zarra]

Did you get ERROR with :

[Weekly Report]:
LOAD
[BFE Responsible],
[Prog Code]
FROM [lib://...]
(ooxml, embedded labels, table is [Weekly Report]);

[RubenMoreno]

Hello @Taoufiq_Zarra ,

No, I don't get any error with the code below.

Best regards,
Rubén

[Taoufiq_Zarra · Accepted solution]

try this version :

Pick(if(match([BFE Responsible], 'OLIVIER','ALEXANDER')>0,1, 
if((match([BFE Responsible], 'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'N')>0),2, 
if((match([BFE Responsible], 'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'P')>0),3, 
if((match([BFE Responsible],'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'R')>0),4, 
if((match([BFE Responsible],'OLIVIER','ALEXANDER')=0 and match([Prog Code], 'L')>0),5 

))))),'Team1','Team2','Team3','Team4','Team5') as [Team],

in the version that you modified you added  ' .

[RubenMoreno]

Hi @Taoufiq_Zarra ,

I am very sorry but I cannot see where I added incorrect ' in my expression.  Could you please tell me where the mistake is so I can correct?

Thanks.

Best regards,
Rubén

[Kushal_Chawda]

try below

=Pick(match(-1,match(ColumnA, 'Maria R','Sebastian L','Isabel S'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB, 'N'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB, 'P'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB, 'R'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB,'L')),
'Team1','Team2','Team3','Team4','Team5') 

[cengizeralp · Accepted solution]

Hi Kush,

Your code is very weel but I think, we should add a second condition to obtain '-1' (true) result in the first line.

=Pick(match(-1,match(ColumnA, 'Maria R','Sebastian L','Isabel S') and ColumnB like '*',
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB, 'N'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB, 'P'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB, 'R'),
not match(ColumnA, 'Maria R','Sebastian L','Isabel S') and match(ColumnB,'L')),
'Team1','Team2','Team3','Team4','Team5')