Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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?

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Partner
Partner

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

View solution in original post