Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RubenMoreno
Contributor III
Contributor III

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

Labels (4)
2 Solutions

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

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  ' .

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

cengizeralp
Contributor III
Contributor III

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') 

 

View solution in original post

8 Replies
Taoufiq_Zarra
Master II
Master II

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,
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
RubenMoreno
Contributor III
Contributor III
Author

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
Master II
Master II

Did you get ERROR with :

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
RubenMoreno
Contributor III
Contributor III
Author

Hello @Taoufiq_Zarra ,

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

Best regards,
Rubén

Taoufiq_Zarra
Master II
Master II

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  ' .

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

RubenMoreno
Contributor III
Contributor III
Author

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

Kush
MVP
MVP

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
Contributor III
Contributor III

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') 

 

View solution in original post