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]

@Ahidhar Super! It works 😁

I have other groups to add to my list, so I just want to make sure I understand the logic of the order.

If I take the example of the 'Mash Ovins/Caprins', you put the single criteria ('Ovins' and 'Caprins') at the end of the code, with the 'and match' at the beginning of the code. 

If I have other single criteria group to create - should they all go at the end of the list of groups? 

The "null()" at the end signifies to leave the cell blank if the customer order does not fit any of the above criteria (similar to a IF formula in Excel)?

Thank you very much for your help and explications!

[Ahidhar]

Yes , thats the logic 

[Ravi_Nagmal]

@Nion Try this:

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

[Pablo007]

if(([ARTICLE.Code présentation]='MS'), 'Mash Ovins/Caprins',
if(([ARTICLE.Code présentation]= 'MH'), 'Mash Ovins/Caprins',
if(left([ARTICLE.Code groupe CPS],3)='033', 'Ovins',
if(left([ARTICLE.Code groupe CPS],2)= '33', 'Ovins',
if(left([ARTICLE.Code groupe CPS],3)='034', 'Caprins',
if(left([ARTICLE.Code groupe CPS],2)= '34', 'Caprins',
if(([ARTICLE.Attribut Label]= '0') and left([ARTICLE.Code groupe CPS],3)='380','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Label]= '0') and left([ARTICLE.Code groupe CPS],3)='344','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Label]= '0') and left([ARTICLE.Code groupe CPS],3)='582','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Certifie]= '0') and left([ARTICLE.Code groupe CPS],3)='380','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Certifie]= '0') and left([ARTICLE.Code groupe CPS],3)='344','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Certifie]= '0') and left([ARTICLE.Code groupe CPS],3)='582','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut bio]= '0') and left([ARTICLE.Code groupe CPS],3)='380','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut bio]= '0') and left([ARTICLE.Code groupe CPS],3)='344','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut bio]= '0') and left([ARTICLE.Code groupe CPS],3)='582','Poulets production sous label (AOC, Label Rouge, Bio)',
'N/A'))))))))))))))) AS New_dimension

[Pablo007]

if(([ARTICLE.Code présentation]='MS'), 'Mash Ovins/Caprins',
if(([ARTICLE.Code présentation]= 'MH'), 'Mash Ovins/Caprins',
if(left([ARTICLE.Code groupe CPS],3)='033', 'Ovins',
if(left([ARTICLE.Code groupe CPS],2)= '33', 'Ovins',
if(left([ARTICLE.Code groupe CPS],3)='034', 'Caprins',
if(left([ARTICLE.Code groupe CPS],2)= '34', 'Caprins',
if(([ARTICLE.Attribut Label]= '0') and left([ARTICLE.Code groupe CPS],3)='380','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Label]= '0') and left([ARTICLE.Code groupe CPS],3)='344','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Label]= '0') and left([ARTICLE.Code groupe CPS],3)='582','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Certifie]= '0') and left([ARTICLE.Code groupe CPS],3)='380','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Certifie]= '0') and left([ARTICLE.Code groupe CPS],3)='344','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut Certifie]= '0') and left([ARTICLE.Code groupe CPS],3)='582','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut bio]= '0') and left([ARTICLE.Code groupe CPS],3)='380','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut bio]= '0') and left([ARTICLE.Code groupe CPS],3)='344','Poulets production sous label (AOC, Label Rouge, Bio)',
if(([ARTICLE.Attribut bio]= '0') and left([ARTICLE.Code groupe CPS],3)='582','Poulets production sous label (AOC, Label Rouge, Bio)',
'N/A'))))))))))))))) AS New_dimension

[Nion]

Hello again @Ahidhar ,

Sorry to bother you, but I seem to be stuck again. 

I have added the entire list of categories that I want to create in my new dimension, and for some reason I have a few of them that are not working. 

I have 4 categories that take into consideration the information in 2 different columns:

- Mash Ovins/Caprins, Mash Vaches Laitières, Mash Autres Bovins and Poulets production sous label (AOC, Label Rouge, Bio)

With the following code, the Mash Ovins/Caprins shows up in the list, as well as Poulets production sous label (AOC, Label Rouge, Bio), but not the other 2.  Using what I thought was the same code structure, I can't get the other 2 to work. There seems to be a logic that I am just not catching on to... 

Would you be able to explain why this is not working?

Any help would be grealtly appreciated!

=
//if(match([ARTICLE.Code présentation], 'MS', 'MH') and match([ARTICLE.Code groupe CPS], '314', '319', '335'),'Mash Vaches Laitières', // --> doesn't show in the list, wrong order compared to Mash Ovins/Caprins
if(match([ARTICLE.Code groupe CPS], '033', '33', '034', '34') and match([ARTICLE.Code présentation], 'MS', 'MH'),'Mash Ovins/Caprins',
//if(match([ARTICLE.Code groupe CPS], '577', '326', '586', '311', '334') and match([ARTICLE.Code présentation], 'MS', 'MH'),'Mash Autres Bovins', //--> doesn't show in the list, same order as 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], '314', '319', '335') and match([ARTICLE.Code présentation], 'MS', 'MH'),'Mash Vaches Laitières', // --> doesn't show in the list, same order as Mash Ovins/Caprins
if(match([ARTICLE.Code groupe CPS], '161'), 'Cheval',
if(match([ARTICLE.Code CPS], '584', '994', '969'), 'Pondeuses d oeufs de consommation',
if(match([ARTICLE.Code CPS], '993'), 'Poulettes filière oeufs de consommation',
if(match([ARTICLE.Code CPS], '232', '336', '377', '455', '585', '543', '461'), 'Poulettes, pondeuses reproducteurs',
if(match([ARTICLE.Code CPS], '304', '833'), 'Porcelets 1er âge',
if(match([ARTICLE.Code analytique], '650', '651', '780', '655', '781', '786'), 'Truies complémentaires',
if(match([ARTICLE.Code analytique], '632', '633', '636', '637', '640', '641', '644', '785'), 'Porcs élevage et engraissements complets',
if(match([ARTICLE.Code analytique], '634', '635', '776', '638', '639', '777', '642', '643', '778', '646', '647', '779'), 'Porcs élevage et engraissement complémentaires',
//if(match([ARTICLE.Code CPS], '579'), 'Porcs élevage et engraissement complémentaires', // --> doesn't show in the list when activated, is it because I have already created the category in the line before? How do I combine the 2? 
if(match([ARTICLE.Code CPS], '303'), 'Porcelets 2ème âge',
if(match([ARTICLE.Code CPS], '315', '316'), 'Gibiers',
if(match([ARTICLE.Code CPS], '317'), 'Cailles',
if(match([ARTICLE.Code CPS], '348', '450', '331', '892', '893', '580'), 'Lapins',
if(match([ARTICLE.Code CPS], '376'), 'Dindes de reproduction',
if(match([ARTICLE.Code CPS], '308'), 'Dindes d engraissement',
if(match([ARTICLE.Code analytique], '517', '518', '519'), 'Palmipèdes gras',
if(match([ARTICLE.Code analytique], '789', '516'), 'Palmipèdes à rôtir',
if(match([ARTICLE.Code analytique], '531', '532', '696'), 'Pintades',
if(match([ARTICLE.Code analytique], '563', '574', '584'), 'Aliments d allaitement',
if(match([ARTICLE.Code analytique], '648', '649', '652', '653'), 'Truies complets',
if(match([ARTICLE.Code CPS], '577', '326', '586', '311', '334'), 'Autres Bovins',
if(match([ARTICLE.Code CPS], '314'), 'Vaches laitières > 30% de MAT (correcteurs azotés)',
if(match([ARTICLE.Code CPS], '335'), 'Vaches laitières de 15 % à 30% de MAT (aliments de production)',
if(match([ARTICLE.Code CPS], '319'), 'Vaches laitières < 15% de MAT (correcteurs énergétiques)',
if(match([ARTICLE.Code CPS], '380', '344', '582'), 'Poulets',
if(match([ARTICLE.Code groupe CPS], '033', '33'), 'Ovins',
if(match([ARTICLE.Code groupe CPS], '034', '34'), 'Caprins', null()
)))))))))))))))))))))))))))))))

[Ahidhar]

try to use only one set of group then go with the next one like 

[ARTICLE.Code groupe CPS] and [ARTICLE.Code présentation]

then next group