Skip to main content
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Pick and Match function for intervals

Hi everyone,

I would like to use the pick and match functions to color background of a field but I do not understand on how to use it. Can someone help me?

So the idea, is to calculate the difference between 2 durations and if result is :

< 20 min -> Green

>= 20 min <  30 min -> Orange

>= 30 min -> Red

Can you help me please ? 

Have a nice day.

Labels (1)
4 Replies
Creator II
Creator II

I don't know your datafields, but you can use If to pick the color for background.

If(Field or Measure < 20, rgb(165,211,0),

IF(Field or Measure >= 20 min and Field or Measur e<30, rgb(250,186,35),

IF(Field or Measure >= 30, rgb(230,74,60))))


Contributor II
Contributor II

Ok for nested if but is there a way to use pick and match ? Just for my understanding? 😄 

Creator II
Creator II

Sure, it is possible but needs extra work.

Just for clarification, pick() chooses an expression of a list based upon the ordinal number given by either a field or another function. pick(n, expr1,expr2,exprN)

Match() returns the location of the searched expression inside the given list. match(Str, expr1,expr2,exprN)


In Code:

match(Field, 'Red','Orange','Green'),


So you have to define a field within your script because match needs a string to compare the list against.

But the moment you define that in the script, you might as well store the rgb(#,#,#) in the field and thus just have to use the field without any pick(match()) at all.



Pick(match()) could be used for such scenarios, for example with:

pick(match(-1, Duration<20, Duration<30, -1),
   color1, color2, color3)

but logically it's the same like the nested if-loops - just using a different syntax which provides mostly a better readability.

Different to it would be if you could apply a logic like:

pick(ceil(Duration / 10), color1, color2, color3)

The second approach based on logic to calculate the pick-index directly. If and how such logic is possible depends on the amount of possible return-values and if the intervals are homogeneous or not - whereby the index-calculation could be of course more complex by using some rangemin/rangemax to fetch the lower/upper outliers, more and other rounding and/or including also some if-branches.

Further it's possible to create an appropriate matching-table within any other tool and loading it into the data-model and a pick(match()) fetched these data within adhoc-variables.

The both last methods could become quite complex but are very helpful if the performance with the simple methods are too slow or if it's just too tedious to create a whole bunch of branches for it.

- Marcus