Nested If statement in Dimension

[jamesrizzitano]

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,

[sunny_talwar · Accepted solution]

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]

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

[sunny_talwar]

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]

Thank You Sunny,

I appreciate your thoughts as I am new to Qlik.

[upaliwije]

Thanks Sunny

[sunny_talwar]

No problem my friend