Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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()
))))))
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!
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
];
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
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 .
load each if statement separately one by one and you will get why it works that way
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!
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()
))))))))))
Thanks @Ahidhar !
I tried the suggested code, but I got the following message: "Error in expression: it takes 2-3 parameters" 🤔
Any insights?
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()
))))))