Pick and Match function Optimized

[triekong]

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?

[rwunderlich]

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

[Vegar · Accepted solution]

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])