Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
triekong
Contributor

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?

1 Solution

Accepted Solutions
Partner
Partner

Re: Pick and Match function Optimized

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])
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
2 Replies
MVP & Luminary
MVP & Luminary

Re: Pick and Match function Optimized

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

Partner
Partner

Re: Pick and Match function Optimized

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])
Plees ekskuse my Swenglish and or Norweglish spelling misstakes