Nested If Statements

[#1]

Hi,

Does anyone know if there is a limit to the number of if statements that can be nested within an expression for a chart in Qlik Sense?  I have 19 and it works fine.  20 seems to make the expression not work, but I wanted to make sure it was a limitation and not something else that perhaps was going wrong.  I couldn't seem to find any other errors, but I am pretty new to this.

Also, if 20 is the limit, does anyone have any ideas on how to get around that limitation in the following scenario?

I have 28 options that can be selected for a variable called Metric.Name

Based on what is selected for Metric.Name, I have if statements controlling the content of a pivot table.  I need 28 if statements to control it for all the variables.  Any ideas?

Thanks!

[marcus_sommer]

Within qlikview there is a limit of 99 nested if-loop. I don't know if such a limit in sense is lesser. But nearly always are better (in developing, maintaining and performance) solutions possible, see here an example: Re: Substitute to nested ifs

- Marcus

[JonnyPoole]

I tried putting > 20 nested IFs and it works. But i think you should look at alternatives too.  Advising on which alternative would require more information .

[subbareddykm]

Hi,

We can use more thn 20.

Can you share your Expression ?

[#5]

Sure, here is my statement so far.  This one works.  Essentially, I have all the "false statements" already pre-written out and then am just pasting them in for the "0" at the end of the expression.  When I paste in the "RN Clinical Justification" part, it no longer works.  That, however, works on it's own.

Works:

if(GetFieldSelections(Metric.Name)='AMI 2', $(vaAMI2("*")), if(GetFieldSelections(Metric.Name)='AMI 3', $(vaAMI3("*")),if(GetFieldSelections(Metric.Name)='AMI 5', $(vaAMI5("*")),if(GetFieldSelections(Metric.Name)='AMI 10', $(vaAMI10("*")),

if(GetFieldSelections(Metric.Name)='VTE 2', $(vaVTE2("*")),if(GetFieldSelections(Metric.Name)='VTE 5', $(vaVTE5("*")),if(GetFieldSelections(Metric.Name)='STK 8', $(vaSTK8("*")),if(GetFieldSelections(Metric.Name)='PN 2', $(vaPN2("*")),

if(GetFieldSelections(Metric.Name)='PN 3A', $(vaPN3A("*")),if(GetFieldSelections(Metric.Name)='PN 7', $(vaPN7("*")),if(GetFieldSelections(Metric.Name)='PC 1', $(vaPC1("*")),if(GetFieldSelections(Metric.Name)='PC 5', $(vaPC5("*")),

if(GetFieldSelections(Metric.Name)='Pain Assessed Every 4 Hrs', $(vaPainAssessed("*")),if(GetFieldSelections(Metric.Name)='Pain Reass. w/in 30 mins for IV/IM', $(vaPainReassessedIV("*")),if(GetFieldSelections(Metric.Name)='Pain Reass. w/in 1 hr non-IV/IM', $(vaPainReassessedNonIV("*")),

if(GetFieldSelections(Metric.Name)='Foley Care Documented', $(vaFoleyCare("*")),if(GetFieldSelections(Metric.Name)='Secure Device Documented', $(vaSecureDevice("*")),if(GetFieldSelections(Metric.Name)='Order Upon Application', $(vaRestraintOrder("*")),

if(GetFieldSelections(Metric.Name)='Face-to-Face', $(vaFacetoFace("*")),0)))))))))))))))))))

Additional parts I wanted to add:

//if(GetFieldSelections(Metric.Name)='RN Clinical Justification', $(vaCliniclJustification("*")),0)

//if(GetFieldSelections(Metric.Name)='Criteria Explained', $(vaCriteriaExplained("*")),0)

//if(GetFieldSelections(Metric.Name)='Debriefing', $(vaDebriefing("*")),0)

//if(GetFieldSelections(Metric.Name)='Q2H Restraint Monitoring', $(vaQ2HMonitoring("*")),0)

So when I replace the 0 and add the RN Clinical Justification section like the statement below, it no longer works.

if(GetFieldSelections(Metric.Name)='AMI 2', $(vaAMI2("*")), if(GetFieldSelections(Metric.Name)='AMI 3', $(vaAMI3("*")),if(GetFieldSelections(Metric.Name)='AMI 5', $(vaAMI5("*")),if(GetFieldSelections(Metric.Name)='AMI 10', $(vaAMI10("*")),

if(GetFieldSelections(Metric.Name)='VTE 2', $(vaVTE2("*")),if(GetFieldSelections(Metric.Name)='VTE 5', $(vaVTE5("*")),if(GetFieldSelections(Metric.Name)='STK 8', $(vaSTK8("*")),if(GetFieldSelections(Metric.Name)='PN 2', $(vaPN2("*")),

if(GetFieldSelections(Metric.Name)='PN 3A', $(vaPN3A("*")),if(GetFieldSelections(Metric.Name)='PN 7', $(vaPN7("*")),if(GetFieldSelections(Metric.Name)='PC 1', $(vaPC1("*")),if(GetFieldSelections(Metric.Name)='PC 5', $(vaPC5("*")),

if(GetFieldSelections(Metric.Name)='Pain Assessed Every 4 Hrs', $(vaPainAssessed("*")),if(GetFieldSelections(Metric.Name)='Pain Reass. w/in 30 mins for IV/IM', $(vaPainReassessedIV("*")),if(GetFieldSelections(Metric.Name)='Pain Reass. w/in 1 hr non-IV/IM', $(vaPainReassessedNonIV("*")),

if(GetFieldSelections(Metric.Name)='Foley Care Documented', $(vaFoleyCare("*")),if(GetFieldSelections(Metric.Name)='Secure Device Documented', $(vaSecureDevice("*")),if(GetFieldSelections(Metric.Name)='Order Upon Application', $(vaRestraintOrder("*")),

if(GetFieldSelections(Metric.Name)='Face-to-Face', $(vaFacetoFace("*")),if(GetFieldSelections(Metric.Name)='RN Clinical Justification', $(vaCliniclJustification("*")),0))))))))))))))))))))

[#6]

After hours of trouble shooting, as soon as I posted this, I realized I missed a letter 'a' in this expression and that is what caused it to not find the it as a variable.  When I was troubleshooting, I didn't miss the 'a' which is why it worked on its own.

//if(GetFieldSelections(Metric.Name)='RN Clinical Justification', $(vaCliniclJustification("*")),0)

That said, I would like to hear more information on a better way to accomplish this.

[#7]

Hi Jonathan,

I realized after hours of searching I had a spelling error.  That said, I posted more information under Subbareddy's response.  I am interested in hearing more information on a better way to do this.

Thanks,

Melissa

[marcus_sommer · Accepted solution]

The if-loops could be replaced with a pick(match()) approach like:

pick(

     match(GetFieldSelections(Metric.Name), 'AMI 2', 'AMI 3', 'AMI 5', 'AMI 10', 'VTE 2', 'VTE 5', ....),

     $(vaAMI2("*")), $(vaAMI3("*")), $(vaAMI5("*")), $(vaAMI10("*")), $(vaVTE2("*")), $(vaVTE5("*")), ...)

- Marcus

[#9]

Thanks, I will try this.  Is this more efficient, performance wise, than the if loops?

[marcus_sommer]

Yes, the performance from a pick(match()) which is in this way a kind of lookup will be significantly better then nested if-loops.

- Marcus