Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ruma_barman
Contributor

Multiple if statement with "or" and "and"

Hi All,

My requirement is to create a field and categorize into 2 -Category 1 and Category 2. 

1)If Code starts with alphabets then

    1.1)if it belongs to Product Group K01 ,it is categorized as Category 1

   1.2) if it belongs to Product Group K05 ,it is categorized as Category 2

2)If Code starts with integer 6

    2.1)if it belongs to Product Group K01 ,it is categorized as Category 1

3)If Code starts with integer 2

    3.1)if it belongs to Product Group K05 ,it is categorized as Category 2

Input:

Product GroupCode
K01YS4341
K05YS4342
K01YT0517
K05YT0518
K01642130
K05642130
K01690802
K05690802
K01290802
K05290802

Output:

Product GroupCodeCategory
K01YS4341Category1
K05YS4342Category2
K01YT0517Category1
K05YT0518Category2
K01642130Category1
K05642130 
K01690802Category1
K05690802 
K01290802 
K05290802Category2
Labels (1)
1 Solution

Accepted Solutions
Mauritz_SA
Contributor III

Re: Multiple if statement with "or" and "and"

Hi there

 

Maybe try this:

if(
left(Code,1)/1>0,
if((left(Code,1)=6) AND [Product Group] = 'K01' ,'Category 1',
if((left(Code,1)=2) AND [Product Group] = 'K05' ,'Category 2',))
,if([Product Group] = 'K01','Category 1',if([Product Group] = 'K05','Category 2')))

 

Regards,

Mauritz

1 Reply
Mauritz_SA
Contributor III

Re: Multiple if statement with "or" and "and"

Hi there

 

Maybe try this:

if(
left(Code,1)/1>0,
if((left(Code,1)=6) AND [Product Group] = 'K01' ,'Category 1',
if((left(Code,1)=2) AND [Product Group] = 'K05' ,'Category 2',))
,if([Product Group] = 'K01','Category 1',if([Product Group] = 'K05','Category 2')))

 

Regards,

Mauritz