topic Re: Pick and Match function Optimized in QlikView

Pick and Match function Optimized

triekong — Sat, 16 Nov 2024 20:37:08 GMT

We are trying to create tooltips(popup messages) that come from an external file. We are avoiding using nested IF as there can be a lot of values and it will affect performance. On the attached samples we are using “Pick Match” with the syntax as:

pick(Match(Type, 'A', 'B', 'C', 'D', 'E'), Only({<[Tooltip of]={A}>} Message), Only({<[Tooltip of]={B}>} Message), Only({<[Tooltip of]={C}>} Message), Only({<[Tooltip of]={D}>} Message), Only({<[Tooltip of]={E}>} Message))

This works as expected, we see the values we want, but I feel like there is a better way to code this more efficiently Thoughts?

Re: Pick and Match function Optimized

rwunderlich — Thu, 20 Jun 2019 21:29:11 GMT

It seems to me you could just add a Category field to your tooltip table and then the value would be automatically linked and your expression would be:

=Message

Or is your real app more complicated and this wouldn't work?

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Pick and Match function Optimized

Vegar — Thu, 20 Jun 2019 21:56:00 GMT

Data islands are tricky hence it is hard to create dimension differenciating set expressions with them. I don't see how you could be more efficient as long you have the comments as a data island.

An alternative solution is to connect your comment table to the data using a link-table.

Main: LOAD Type, Category, Business, Type&'|'&Category&'|'&Business as %key, Amount FROM [Main dataset.xlsx] (ooxml, embedded labels, table is Sheet1); //Creating a link table for each _type in 'Category', 'Business', 'Type' LOAD distinct [$(_type)] as [Tooltip of], %key, '$(_type)' as [Tooltip class] RESIDENT Main ; NEXT Tooltips: LOAD [Tooltip of], Message FROM [Tooltips mapping.xlsx] (ooxml, embedded labels, table is Sheet1) ;

You get the Tooltip per dimension in your Type chart with this expression:

=Type & chr(10) & sum(Amount) & chr(10) & only({< [Tooltip class] = {'Type'}>}[Message])