Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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?

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

6 Replies
MVP
MVP

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:

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.

Not applicable

Re: Maximum Number of Conditions to a Nested If Statement

Use ApplyMap Approach suggested Manish

Not applicable

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

Not applicable

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.

MVP
MVP

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

Not applicable

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

Community Browser