Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jamesrizzitano
New Contributor II

Nested If statement in Dimension

I have this nested if statement that does not seem to be working in qliksense.

Can you tell me what's wrong with it.  The error says I'm missing a ")" but I can't see where.

=if(left(ProdCode, 2) = '1I' or left(ProdCode, 2) = '2I' or left(ProdCode, 2) = '3I' or left(ProdCode, 2) = '4I' or

left(ProdCode, 2) = '5I' or left(ProdCode, 2) = '6I' or left(ProdCode, 2) = '8I' or left(ProdCode, 2) = '9I','Instruments',

if(left(ProdCode, 2) = '1C' or left(ProdCode, 2) = '2C' or left(ProdCode, 2) = '3C' or left(ProdCode, 2) = '4C' or

left(ProdCode, 2) = '5C' or left(ProdCode, 2) = '6C' or left(ProdCode, 2) = '8C' or left(ProdCode, 2) = '9C','Consumables',

if(left(ProdCode, 2) = '1P' or left(ProdCode, 2) = '2P' or left(ProdCode, 2) = '3P' or left(ProdCode, 2) = '4P' or

left(ProdCode, 2) = '5P' or left(ProdCode, 2) = '6P' or left(ProdCode, 2) = '8P' or left(ProdCode, 2) = '9P','Parts',

if(left(ProdCode, 2) = '1O' or left(ProdCode, 2) = '2O' or left(ProdCode, 2) = '3O' or left(ProdCode, 2) = '4O' or

left(ProdCode, 2) = '5O' or left(ProdCode, 2) = '6O' or left(ProdCode, 2) = '8O' or left(ProdCode, 2) = '9O','Obsolete',

if(left(ProdCode, 2) = '1M' left(ProdCode, 2) = '2M' or left(ProdCode, 2) = '3M' or left(ProdCode, 2) = '4M' or

left(ProdCode, 2) = '5M' or left(ProdCode, 2) = '6M' or left(ProdCode, 2) = '8M' or left(ProdCode, 2) = '9M,'NonStock Svc',

if(left(ProdCode, 2) = '1S' left(ProdCode, 2) = '2S' or left(ProdCode, 2) = '3S' or left(ProdCode, 2) = '4S' or

left(ProdCode, 2) = '5S' or left(ProdCode, 2) = '6S' or left(ProdCode, 2) = '8S' or left(ProdCode, 2) = '9S','Ship',

if(left(ProdCode, 2) = '1T' left(ProdCode, 2) = '2T' or left(ProdCode, 2) = '3T' or left(ProdCode, 2) = '4T' or

left(ProdCode, 2) = '5T' or left(ProdCode, 2) = '6T' or left(ProdCode, 2) = '8T' or left(ProdCode, 2) = '9T','Loaners',

'Misc'))))))) as ProdGroup

Thanks for the Help,

1 Solution

Accepted Solutions

Re: Nested If statement in Dimension

May be try this

If(Match(Left(ProdCode, 2), '1I', '2I', '3I', '4I', '5I', '6I', '8I', '9I'), 'Instruments',

If(Match(Left(ProdCode, 2), '1C', '2C', '3C', '4C', '5C', '6C', '8C', '9C'), 'Consumables',

If(Match(Left(ProdCode, 2), '1P', '2P', '3P', '4P', '5P', '6P', '8P', '9P'), 'Parts',

If(Match(Left(ProdCode, 2), '1O', '2O', '3O', '4O', '5O', '6O', '8O', '9O'), 'Obsolete',

If(Match(Left(ProdCode, 2), '1M', '2M', '3M', '4M', '5M', '6M', '8M', '9M'), 'NonStock Svc',

If(Match(Left(ProdCode, 2), '1S', '2S', '3S', '4S', '5S', '6S', '8S', '9S'), 'Ship',

If(Match(Left(ProdCode, 2), '1T', '2T', '3T', '4T', '5T', '6T', '8T', '9T'), 'Loaners', 'Misc'))))))) as ProdGroup

Or just this

Pick(WildMatch(Left(ProdCode, 2), '*I', '*C', '*P', '*O', '*M', '*S', '*T') + 1, 'Misc', 'Instruments', 'Consumables', 'Parts', 'Obsolete', 'NonStock Svc', 'Ship', 'Loaners') as ProdGroup

6 Replies

Re: Nested If statement in Dimension

May be try this

If(Match(Left(ProdCode, 2), '1I', '2I', '3I', '4I', '5I', '6I', '8I', '9I'), 'Instruments',

If(Match(Left(ProdCode, 2), '1C', '2C', '3C', '4C', '5C', '6C', '8C', '9C'), 'Consumables',

If(Match(Left(ProdCode, 2), '1P', '2P', '3P', '4P', '5P', '6P', '8P', '9P'), 'Parts',

If(Match(Left(ProdCode, 2), '1O', '2O', '3O', '4O', '5O', '6O', '8O', '9O'), 'Obsolete',

If(Match(Left(ProdCode, 2), '1M', '2M', '3M', '4M', '5M', '6M', '8M', '9M'), 'NonStock Svc',

If(Match(Left(ProdCode, 2), '1S', '2S', '3S', '4S', '5S', '6S', '8S', '9S'), 'Ship',

If(Match(Left(ProdCode, 2), '1T', '2T', '3T', '4T', '5T', '6T', '8T', '9T'), 'Loaners', 'Misc'))))))) as ProdGroup

Or just this

Pick(WildMatch(Left(ProdCode, 2), '*I', '*C', '*P', '*O', '*M', '*S', '*T') + 1, 'Misc', 'Instruments', 'Consumables', 'Parts', 'Obsolete', 'NonStock Svc', 'Ship', 'Loaners') as ProdGroup

upaliwije
Contributor II

Re: Nested If statement in Dimension

Hi Sunny

Pick(WildMatch(Left(ProdCode, 2), '*I', '*C', '*P', '*O', '*M', '*S', '*T') + 1, 'Misc', 'Instruments', 'Consumables', 'Parts', 'Obsolete', 'NonStock Svc', 'Ship', 'Loaners') as ProdGroup

In the above Expression Why +1 is added and 'Misc' Comes first. Pls explain

Re: Nested If statement in Dimension

Basically WildMatch gives value 1, 2, 3, 4 based on your condition.... so when nothing matches you get a 0. So 0+1 = 1, and pick uses this to determine the output.... so 1 is Misc, 2 is Instruments and so on....

jamesrizzitano
New Contributor II

Re: Nested If statement in Dimension

Thank You Sunny,

I appreciate your thoughts as I am new to Qlik.

upaliwije
Contributor II

Re: Nested If statement in Dimension

Thanks Sunny

Re: Nested If statement in Dimension

No problem my friend

Community Browser