Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jamesrizzitano
Contributor II
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
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

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
Creator II
Creator II

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

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
Contributor II
Contributor II
Author

Thank You Sunny,

I appreciate your thoughts as I am new to Qlik.

upaliwije
Creator II
Creator II

Thanks Sunny

sunny_talwar

No problem my friend