Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
triekong
Creator
Creator

Pick and Match function Optimized

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?

Labels (7)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

 

 

image.png

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

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
MVP
MVP

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

 

 

image.png

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