Error in qlikview -> Error in expression:Too complex, more than 100 levels nesting

[sonysree88]

Hi All,

Need help .its very urgent.

I'm using 300 IF statements in the Qlikview load statement and receiving the below error message. I've googled the error message and have read in some blogs and some one stated that there are only 100 IF statements allowed in the load statement and ts the qlik limitation and instead of if need to use pick function.

attached variable . Can you please look into that an send the correct expression which we should use.

Below is mycase.

I have 3 variables

All the expressions which we are maintaining in one excel file which we are using in script and am not understanding why if(wildmatch added to each one)

attached one variable  where am getting the error.

report  - used more than 100 IF statements

report1 - used more than 50 if statements

report2- Used more than 20 if statements

These 3 variables in script we have used like as below .

NoConcatenate
Data1:

LOAD *,
$(l.Report) AS %KEY_ReportMgt1,
$(l.Report1) AS %KEY_ReportMgt2,
$(l.Report2) AS %KEY_ReportMgt3
Resident tmp1Data;

DROP Table tmp1Data;
//Combine 2 Keys
NoConcatenate
Data2:
LOAD *,
ApplyMap('MAP_GlobalSign',if(not isnull(%KEY_Report1), %KEY_Report1, if(not isnull(%KEY_Report2), %KEY_Report2, %KEY_Report3)),1)*#BAlance AS Balance,

ApplyMap('MAP_GlobalSign',if(not isnull(%KEY_Report1), %KEY_Report1, if(not isnull(%KEY_Report2), %KEY_Report2, %KEY_Report3)),1)*#CashFlowAmountT AS #CashFlow,
if(not isnull(%KEY_Report1), %KEY_Report1,
if(not isnull(%KEY_Report2), %KEY_Report2, %KEY_Report3)
) as %KEY_Report

Resident Data1;

DROP Table Data1;

How can i resolve this issue and it would be great if you help asap.

Thanks in advance.

Regards,

Sonysree

@sunny_talwar - Can you modify my expression with pick and send me back so that i can check.

I tried with pick but not working fine for me as i seen your post in some other post.

https://community.qlik.com/t5/QlikView-App-Development/Error-in-expression-Too-complex-more-than-100...

[sonysree88]

Please correct the below expression and send it back please.

[marcus_sommer]

I think you are running in the totally wrong direction. The idea behind Qlik and any other BI tool is to develop a datamodel of properly associated/linked dimensions/tables which is mostly done by rather generic approaches and not picking/matching any relations with heavily nested if-loops ...

Beside these IMO not too restrictive technically limitations I'm sure that such if-loops needs a lot more efforts to develop and check them as implementing a bit more advanced datamodel with one or two more logically layers.

Further as far as your dataset increased a bit you will get easily performance issues and maintaining it will become a nightmare ... Therefore I suggest to re-think the whole approach.

- Marcus

[sonysree88]

Its working fine earlier. just we did the change in the same expression to add one more if statement thats it for SB.

if you see the last IF statement is that only added as per the enhancement request.

[Brett_Bleess]

Found one of Sunny's old posts that may have what you need:

https://community.qlik.com/t5/QlikView-App-Development/Error-in-expression-Too-complex-more-than-100...

Regards,
Brett