
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Issue with expression containing several "IFs"
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
- Tags:
- if
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ' .
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @Taoufiq_Zarra ,
I tried to apply the formula above to my problem and I get the following message
[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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Did you get ERROR with :
[Weekly Report]:
LOAD
[BFE Responsible],
[Prog Code]
FROM [lib://...]
(ooxml, embedded labels, table is [Weekly Report]);
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ' .
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')
