Too complex, more than 100 levels nesting

[#1]

I have a complex application in QV8.5 (running on MS Server 2003) that (unfortunately) has a nested "if" statement to fill in a field of a generated table that is 301 levels deep! I know this is crazy...but it runs in QV8.5 with no errors.

However, when run in QV10 (running on MS Server 2008), it chokes on the nested "if" statement and throws the error "Too complex, more than 100 levels nesting".

Is this a QV10 problem? Will I be forced to find a more efficient way of fill in the field? And...what may this more efficient method be? Create a table and join on the 5 fields it is testing to populate the new field? Loop thru a matrix?

Looking for some advice from the QV brotherhood. Thanx.

[johnw]

Well, I've never heard of that error, and am disappointed if we're now limited to 100 levels deep for if() nesting in QlikView. I thought we didn't have any sort of artificial limitations like that.

Anyway, what are you DOING with the if? Yes, there are probably more efficient ways to do what you're doing, but I'm not sure what you're doing. Is it in the script? Something like this?

if(f1='a' and f2='b' and f3='c' and f4='d' and f5='e', 'something'
,if(f1='p' and f2='q'... , 'something else'
,if(...
...
)))))))))))))))))))))))))))))... as "New Field"

If so, are all the tests for "equals"? Do some use other conditions? Do you use wildmatches? Is the logic arbitrarily complex, or is there a pattern to it?

Can you create a test application with some sample data and your nested if?

[#3]

John,

Your right on...it's a multi field test to assess a value to a new field...just as you describe. It runs fine in QV 8.5 but not in QV 10. No fancy stuff, very straight forward "if" statements. No wild cards, all are equals, nothing arbritrarily complex. Wonder if it has something to do with MS Server 2008?

Thanx for the response...new pic looks good.

[johnw]

Ron Sunter wrote:Your right on...it's a multi field test to assess a value to a new field...just as you describe. It runs fine in QV 8.5 but not in QV 10. No fancy stuff, very straight forward "if" statements. No wild cards, all are equals, nothing arbritrarily complex. Wonder if it has something to do with MS Server 2008?
Thanx for the response...new pic looks good.

Heh, and then I changed the pic again this morning, so the pic you're talking about is no longer up. I don't think I was smiling in the other picture. I almost seemed to be scolding people for daring to ask questions on a forum, or at least that's how it looked to me. *chuckle* I wanted a friendlier photo. Hopefully this one looks friendlier, even if it's less businesslike since I'm wearing a T-shirt.

Anyway, back on subject...

Yes, I think the better way to handle it is just what you guessed - create a table and join on the five fields:

LEFT JOIN (MyMainData)
LOAD * INLINE [
f1, f2, f3, f4, f5, Result
a, b, c, d, e, something
p, q, r, s, t, something else
etc.
];

Seems like that should work fine, and will probably be easier to maintain than the if(). Might even load faster too, though it might not.

Another option if load speed is a problem, and again I don't know if this would be faster, would be to concatenate the fields and use a normal mapping load. Something like this:

[MappingRule]:
LOAD * INLINE [
KeyValues, Result
a:b:c:d:e, something
p:q:r:s:t, something else
etc.
];
MAP Result USING MappingRule
;
[MyMainData]:
LOAD
...
,f1 & ':' & f2 & ':' & f3 & ':' & f4 & ':' & f5 as Result
...
;

I haven't actually tried either of these alternatives, but hopefully the ideas are sound and the syntax correct. Wouldn't surprise me if you need to play with them a bit, though.

[#5]

Once again...right on. That was my next try....build a unique key by concatenating the fields together and create a mapping. This is a good work around...but I'm still dumbfounded why the "if" statement works in QV8.5 but not in QV10.

I'm raising this as a Case as we speak. More later.

[johnw]

Yeah, I'm dumbfounded too. I think the join or the map is a better solution, but I still can't imagine what QlikTech was thinking if they really did just add a 100 deep limit to if() nesting like that. There seems no need for a limit, and it's a horrible idea to suddenly add one from a backwards-compatibility standpoint. So I'm curious to see what they tell you in response to your case.

[#7 · Accepted solution]

Got a reply from QlikView Support on my case. They say that "the change in levels of nesting is no bug, this was changed in QV9". They go on to say that because there was no documentation on this, they have registered it as a bug (#36289) and, if approved, may fix it in a future Service Release.

So, I will have to do some program changes. I didn't like the way the original author wrote the code anyway. A 300+ level nested IF statement is just wrong.

By the way, they didn't say what the current limit is for nested IF statements. I guess from the error message, it can not exceed 100. Thanx for your help.

[pgriffiths]

I found a workaround for this, just keep Top loading.

I load my fields and first 90 if statements and then on the last of the nested if statements, I end with everything else set to z_Other.

I then Top load, load the field if the field is not z_Other, then continue for the next 90 nexted if's, then again everything else set to z_Other, but as filed_1

I then Top load again and again over and over as Field_2 Field_3 etc....

At the end, you can drop all the unwanted fields and just keep the last one. I call my last one c_Field to show its calculated.

Drop Fields Field_0, Field_1, Field_3, Field_4;

etc...

Works great.

Here is some example code.

QUALIFY *;
 incident:
 
 LOAD *,
 if(Left(Product_1,6)<>'zOther', Product_1, 
 if(WildMatch( short_description, '*data*room*')>0, 'Data Room',
 if(WildMatch( short_description, '*lotus*notes*')>0, 'Lotus Notes',
 'zOther'
 ))) as c_Product;
 
 LOAD *,
 if(Left(Product_2,6)<>'zOther', Product_2, 
 if(WildMatch( short_description, '*DropBox*', '*Drop?Box*')>0, 'DropBox',
 if(WildMatch( short_description, '*Dead*Call*', '*dropped*call*', '*call*dropped*', '*silent*call*')>0, 'Dropped Call',
 'zOther'
 ))) as Product_1;
 
 LOAD *,
 if(Left(Product_3,6)<>'zOther', Product_3, 
 if(WildMatch( short_description, '*Internet*', '*www.*', '*website*', '*http*', '*page*can*t*be*displayed*', '*Browsing*slow*')>0, 'Internet',
 if(WildMatch( short_description, '*Java*')>0, 'Java',
 'zOther'
 ))) as Product_2;
 
 LOAD *,
 if(WildMatch( short_description, '*Excel*')>0, 'Ms Excel',
 if(not(WildMatch( short_description, '*password*')>0) AND (WildMatch( short_description, '*Word*')>0), 'Ms Word',
 if(WildMatch( short_description, '*Powerpoint*', '*power point*')>0, 'Ms Powerpoint',
 if(WildMatch( short_description, '*Visio*')>0, 'Ms Visio',
 if(WildMatch( short_description, '*MS*Access*', '*microsoft*access*')>0, 'Ms Access',
 'zOther'
 ))))) as Product_3;
 
 LOAD *;
 SQL SELECT opened_at, short_description
 FROM incident
 ;
 
 Drop Fields incident.Product_3, incident.Product_2, incident.Product_1;
 

[Anonymous]

Hello all,

I am using more than 100 nested if's (135) in the Background color attribute of a straight table, in the IF expression I have 2 variables and some other conditions like below.

if(Variable1<variable2 and field1='x'and field2>0,Green())

The if conditions satisfies for the first 99 if's and then stops from 100, is there any other way to get the color for the rest if's since I'm using the variables. Any help is appreciated.

Thanks in advance.

[rwunderlich]

You could try changing to a pick(match()) syntax like this:

=pick(

match(-1

,Dim1='C' AND sum(Expression1)>22

,Dim1='B' AND TransID=1

,sum(Expression1) > $(var1)

)

,green()

,red()

,blue()

)

-Rob

http://masterssummit.com

http://robwunderlich.com