Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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
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....
Thank You Sunny,
I appreciate your thoughts as I am new to Qlik.
Thanks Sunny
No problem my friend