Maximum Number of Conditions to a Nested If Statement

[#1]

Hi Everyone,

I'm a long time lurker, first time poster--I'd like to thank you all for the invaluable support I've received here over the years.

I am working to group a number of part numbers under a shared name using an nested if statement.  I cannot access the load sessions to our documents, so this is the only option I'm aware of to accomplish this.  The statement below appears to be working for most part numbers, but cuts off around line 50.  Syntax on the part numbers appears correct, and if I break this into multiple if statements, all sections work correctly.  My question is: is there a limit to the number of conditions to a nested if statement, or is my syntax off in this code?

Thanks in advance for all of your help!

Code:

=if(Item='R1700914L1','1540',

//1600

if(Item='1700913F1','1600',

//1940

IF(Item='1700952F1','1940',

IF(Item='B1700952F1','1940',

//1800/1840

IF(Item='1700910F1','1800/1840',

IF(Item='1700912F1','1800/1840',

IF(Item='1700911F1','1800/1840',

IF(Item='B1700910F1','1800/1840',

IF(Item='B1700911F1','1800/1840',

//1920/1925

IF(Item='1700954F1','1920/1925',

IF(Item='1700955F1','1920/1925',

IF(Item='1700950F1','1920/1925',

IF(Item='1700951F1','1920/1925',

//Accessory

IF(Item='1700937F1','Accessory',

IF(Item='1700935G1','Accessory',

IF(Item='1700939F1','Accessory',

IF(Item='1700936G1','Accessory',

IF(Item='1951905G1','Accessory',

IF(Item='1700922F1','Accessory',

IF(Item='1700928F1','Accessory',

IF(Item='1700929F1','Accessory',

IF(Item='1700932F1','Accessory',

IF(Item='1700931F1','Accessory',

IF(Item='1700930F1','Accessory',

IF(Item='1700924F1','Accessory',

IF(Item='1700925F1','Accessory',

IF(Item='1700926F1','Accessory',

IF(Item='4700926F1#EUR','Accessory',

IF(Item='1700923F1','Accessory',

IF(Item='1700920F1','Accessory',

IF(Item='1700921F1','Accessory',

IF(Item='1700900F1','Accessory',

IF(Item='1951900G1','Accessory',

//AP License

if(Item='1951901g1','AP License',

//BSC

if(Item='1700902F1','BSC',

IF(Item='1700903F1','BSC',

IF(Item='1700904G1','BSC',

IF(Item='1700905G1','BSC',

IF(Item='1700906G1','BSC',

IF(Item='1700906G2','BSC',

IF(Item='1700906G3','BSC',

//License

IF(Item='1951904G1','License',

if(Item='1951911','License',

//Netvanta 160

if(Item='1700416F1','Netvanta 160',

if(Item='1700417f1','Netvanta 160',

//ProCare BSC

if(Item='1100AMBSC32M1T1','ProCare BSC',

if(Item='1100AMBSC52M1T1','ProCare BSC',

//ProCare vWLAN

IF(Item='1100AM580101B','ProCare vWLAN',

if(Item='1100AM580302B','ProCare vWLAN',

if(Item='1100AM580110V','ProCare vWLAN',

IF(Item='1100AM580310V','ProCare vWLAN',

IF(Item='1100AM500116V','ProCare vWLAN',

IF(Item='1100AM580311V','ProCare vWLAN',

IF(Item='1100AM780121B','ProCare vWLAN',

IF(Item='1100AM780322B','ProCare vWLAN',

IF(Item='1100AM500111V','ProCare vWLAN',

IF(Item='1100AMWIF1M5T1','ProCare vWLAN',

IF(Item='1100AMWIF2M5T3','ProCare vWLAN',

IF(Item='1100AMBSCAM3T1','ProCare vWLAN',

IF(ITEM='1100AMWIF1M3T3','ProCare vWLAN',

IF(Item='1100AMBSC6M3T1','ProCare vWLAN',

IF(Item='1000AMBSC52M3T3''ProCare vWLAN',

IF(Item='1100AMBSCAM1T1','ProCare vWLAN',

IF(Item='1100AMBSC52M3T3','ProCare vWLAN',

IF(ITEM='1100AMBSCAM1T1','ProCare vWLAN',

IF(Item='1100AMBSCAN1T3','ProCare vWLAN',

IF(Item='1100AMVSC6M1T1','ProCare vWLAN',

IF(Item='1100AMWIF2M1T1','ProCare vWLAN',

IF(Item='1100AMBSCAM4T1','ProCare vWLAN',

IF(Item='1100AMWIF1M4T3','ProCare vWLAN',

IF(Item='1100AMWIF2M4T3','ProCare vWLAN',

IF(Item='1100AMBSC32M2T1H','ProCare vWLAN',

IF(Item='1100AMBSCAM2T1','ProCare vWLAN',

IF(Item='1100AMSCAM2T3','ProCare vWLAN',

IF(Item='1100AMBSC6M3T1','ProCare vWLAN',

IF(Item='1100AMBSC12M3T1','ProCare vWLAN',

IF(Item='1100AMBSC12M3T3','ProCare vWLAN',

//ProCloud

IF(Item='1100MSCM300112','ProCloud',

if(Item='1100MSPONBFAP','ProCloud',

IF(Item='1100MSPH300112','ProCloud',

IF(Item='1100MSPH200112','ProCloud',

IF(Item='1100MSPH200136','ProCloud',

IF(Item='1110MSPH200160','ProCloud',

IF(Item='1100MSPH200112NF','ProCloud',

//ProServices

IF(Item='1100AMWIF3M1T1','ProServices',

if(Item='1100AMWIF3M1T3','ProServices',

IF(Item='1100AMWIF3M2T1','ProServices',

IF(Item='1100AMWIF3M2T3','ProServices',

IF(Item='1100AM701102B','ProServices',

IF(Item='1100AM700311V','ProServices',

IF(Item='1951921G1','ProServices',

IF(Item='1951902G1','ProServices',

IF(Item='1100ALE1001WIF1','ProServices',

IF(Item='1100ALR0001WIFA','ProServices'))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

[MK_QSL]

There is an easy way compare to what you have described above...

It is called as Mapping Load...

Create an excel file of all Item and it's type and you can use as below

Script

=====

MapTable:

Mapping Load * Inline

[

  Item, Type

  R1700914L1, 1540

  1700913F1, 1600

  1700952F1, 1940

  B1700952F1, 1940

  1700910F1, 1800/1840

];

Main:

Load *, ApplyMap('MapTable',Item,'Unknown') as Type Inline

[

  Item

  R1700914L1

  1700913F1

  1700952F1

  B1700952F1

  1700910F1

];

=====

This is just an example with Few Item and Type. Let me know if you still found any difficulties to use this method.

[#3]

Use ApplyMap Approach suggested Manish

[#4]

Thanks Manish,

I do not have access to the QVD itself, only the document on an online access point.  Can I use a similar method as a calculted dimension within a chart or multi box?

Thanks,

Daniel

[#5]

Hi Daniel, ApplyMap is the script function only. Please get the access to QVD and apply the transformation on the script level.

[MK_QSL]

You can use Calculated Dimension as below but not suggested due to performance issue....

But obviously you can try...

=PICK(Match(Item,'R1700914L1','1700913F1','1700952F1','B1700952F1','1700910F1'),'1540','1600','1940','1940','1800/1840')

[#7]

Daniel,

how is it possible that you can't give excel file and ask your application provider to load it into qvd?

Anyway, i understand, that you are power user and you are able to write own expressions.

Maybe Dynamic Update will work for you (if it is turned on in your document)...

regards

Darek