Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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 .
Hi,
We can use more thn 20.
Can you share your Expression ?
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))))))))))))))))))))
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.
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
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
Thanks, I will try this. Is this more efficient, performance wise, than the if loops?
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