6 Replies Latest reply: Apr 17, 2014 5:15 PM by Dariusz Mielczarek

# Maximum Number of Conditions to a Nested If Statement

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?

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',

//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',

//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'))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

• ###### Re: Maximum Number of Conditions to a Nested If Statement

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:

[

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.

• ###### Re: Maximum Number of Conditions to a Nested If Statement

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

• ###### Re: Maximum Number of Conditions to a Nested If Statement

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

• ###### Re: Maximum Number of Conditions to a Nested If Statement

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')

• ###### Re: Maximum Number of Conditions to a Nested If Statement

Use ApplyMap Approach suggested Manish

• ###### Re: Maximum Number of Conditions to a Nested If Statement

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