
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
IF statement - if 2 conditions are met 'A', if only 1 condition is met 'B'
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!
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

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

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

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

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
load each if statement separately one by one and you will get why it works that way

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @Ahidhar !
I tried the suggested code, but I got the following message: "Error in expression: it takes 2-3 parameters" 🤔
Any insights?

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

- « Previous Replies
-
- 1
- 2
- Next Replies »