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'
)))))))))))))))))))))))))))))... 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?
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.
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
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:
LOAD * INLINE [
p:q:r:s:t, something else
MAP Result USING MappingRule
,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.
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.
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.
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;
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;
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.