topic Re: Alternate method for Pick Match in QlikView

Alternate method for Pick Match

krupasaim — Fri, 20 Mar 2020 12:55:27 GMT

Hi All,

We are trying to optimize the expression, we are having 43 rows in a straight table each row has its own expression.

Please refer Table2(Straight table) in the attached QVW, Column1 is Inline Table field, Column2 is the Expression.(have taken only 2 rows in this example).

As of now we are using Pick(Match()).

Can anyone suggest any better option?

Thanks

Re: Alternate method for Pick Match

dplr-rn — Fri, 20 Mar 2020 14:16:17 GMT

Pickmatch is the most efficient way of doing this IMO (compared to if statements)

check

http://dailyqv.blogspot.com/2013/01/using-pick-and-match-functions-for.html

Re: Alternate method for Pick Match

rwunderlich — Fri, 20 Mar 2020 17:57:17 GMT

You will encounter performance problems because both count expressions will be calculated in all cases. The Pick(Match()) will only select one of the results.

A better way to do this is move the Pick(Match()) into a variable (or double DSE) that returns the string value of the selected expression and then calculate it. See https://qlikviewcookbook.com/2014/12/how-not-to-choose-an-expression/

-Rob

Re: Alternate method for Pick Match

MikeW — Sat, 21 Mar 2020 14:58:06 GMT

Rob, I think because the $(=...) is evaluated ignoring dimensions in the table, it would not work in this case since the 'Field' is actually a dimension. It would be awesome if there was a way around that...

I think if the expressions are nicely cacheable, then calculating all cases and then picking one may be ok. They will all need to be calculated on the other rows anyways.

Re: Alternate method for Pick Match

rwunderlich — Sat, 21 Mar 2020 17:37:10 GMT

Good point. If I can think if a syntax that would work, I'll post back.

-Rob