IF statement - if 2 conditions are met 'A', if only 1 condition is met 'B'

[Nion]

Hello everyone,

I am new to Qlik and am already expected to be an expert at work 😅, so I am calling all experts for help.

I am creating a new dimension in order to classify customer orders depending on certain criteria. I figured out how to do it with one or 2 conditions, but now I am stuck !

The outcome that I am looking for is:

- if code groupe CPS is either 033 or 33, it is classified in the 'Ovins' group

- if code groupe CPS is either 034 or 34, it is classified in the 'Caprins' group

- but if the code presentation matches MS or MH, AND the code groupe CPS is one found in my list (033, 33, 034, 34), I would like for that client order to be classified in the 'Mash Ovins/Caprins' group

Here is the code that I tried:

=if(match([ARTICLE.Code groupe CPS], '033', '33'), 'Ovins',
if(match([ARTICLE.Code groupe CPS], '034', '34'), 'Caprins',
if(match([ARTICLE.Code présentation], 'MS', 'MH'), if(match([ARTICLE.Code groupe CPS], '033', '33', '034', '34'), 'Mash Ovins/Caprins',
))))

At the moment, the result I am getting is just 'Ovins' and 'Caprins', there is no one in the 'Mash Ovins/Caprins' group as there should be. Each line works separately, but not when combined. I tried putting the code in the opposite order, without any changes.

Any help would be greatly appreciated!

[Nion]

Ok, so I kept trying things and managed to get the hoped result by changing the order of the script like this:

=if(match([ARTICLE.Code groupe CPS], '033', '33', '034', '34'), if(match([ARTICLE.Code présentation], 'MS', 'MH'), 'Mash Ovins/Caprins',
if(match([ARTICLE.Code groupe CPS], '033', '33'), 'Ovins',
if(match([ARTICLE.Code groupe CPS], '034', '34'), 'Caprins',
))))

Could someone explain why this order works but not the other? What is the order of operations in Qlik? 

Thanks in advance!

[Ahidhar]

try this

tab2:
load Customer,CodeGroupeCPS,CodePresentation,
if(CodePresentation='MS' or CodePresentation='MH' and match(CodeGroupeCPS,033,034,33,34),'Ovins/Caprins',
if(match(CodeGroupeCPS,033,33),'Ovins',
if(Match(CodeGroupeCPS,034,34),'Caprins',null()))) as group;
load * inline
[
Customer,CodeGroupeCPS,CodePresentation
1,033,MS
2,034,MH
3,33,MS
4,34,MH
5,033,NH
6,034,KJ
];

[Pablo007]

Dear @Nion ,

I am not an expert but I will try to help

Are you talking about the Script or a chart on the dashboard?

Can you send a picture of and example of  the data?

Thanks

[Ahidhar]

if you go the way you originally went it will read first two if statements where you are checking Code Groupe CPS

for (033,33,034,34) then it will assign Ovins or Caprins  , these also include rows where Code Presentation is MS OR MH , now when you go for the third if statement it will check both CodePresentation and Code Groupe CPS 

but  columns with(033,33,034,34) are already assigned (Ovins or Caprins) so you won't get the desired result

Reverse the order the first if statement checks both columns and only those columns where CodePresentation  is not MS OR MH will not be assigned now those rows will be filled with Ovins or Caprins as desired .

[Ahidhar]

load each if statement separately one by one and you will get why it works that way 

[Nion]

Hello @Pablo007 ,

Thanks for your reply! 

For the moment, I am trying to create a dimension in order to classify customer orders using different criteria. This dimension will then be used in furture tables / charts. 

Here is a photo of my table which I am using to create my dimension.

And here is the code that I am slowly adding to in the field of my new dimension:

=if(match([ARTICLE.Code groupe CPS], '033', '33', '034', '34'), if(match([ARTICLE.Code présentation], 'MS', 'MH'), 'Mash Ovins/Caprins',
if(match([ARTICLE.Code groupe CPS], '033', '33'), 'Ovins',
if(match([ARTICLE.Code groupe CPS], '034', '34'), 'Caprins',
if(match([ARTICLE.Attribut Label], 'O'), if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
if(match([ARTICLE.Attribut Certifie], 'O'), if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
if(match([ARTICLE.Attribut bio], 'O'), if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
))))))))))

I have managed to get throught the first 3 if statements (and it works like I hoped), but am struggling with the next group.

Any insights are welcome! Thanks again!

[Ahidhar]

try this

=if(match([ARTICLE.Code groupe CPS], '033', '33', '034', '34') and  if(match([ARTICLE.Code présentation], 'MS', 'MH'), 'Mash Ovins/Caprins',
if(match([ARTICLE.Code groupe CPS], '033', '33'), 'Ovins',
if(match([ARTICLE.Code groupe CPS], '034', '34'), 'Caprins',
if(match([ARTICLE.Attribut Label], 'O') and  if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
if(match([ARTICLE.Attribut Certifie], 'O') and if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
if(match([ARTICLE.Attribut bio], 'O') and  if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)', null()
))))))))))

[Nion]

Thanks @Ahidhar ! 

I tried the suggested code, but I got the following message: "Error in expression: it takes 2-3 parameters" 🤔

Any insights?

[Ahidhar · Accepted solution]

try this

if(match([ARTICLE.Code groupe CPS], '033', '33', '034', '34') and match([ARTICLE.Code présentation], 'MS', 'MH'),'Mash Ovins/Caprins',
if(match([ARTICLE.Attribut Label], 'O') and match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
if(match([ARTICLE.Attribut Certifie], 'O') and match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',
if(match([ARTICLE.Attribut bio], 'O') and match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets production sous label (AOC, Label Rouge, Bio)',

if(match([ARTICLE.Code groupe CPS], '033', '33'), 'Ovins',
if(match([ARTICLE.Code groupe CPS], '034', '34'), 'Caprins',null()
))))))