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

How to get the Dynamic colors in Pivot table by using pick and match functions


i am using below nested if condition to show colors

If( sum([Males Planted]) <= 100, RGB(115, 150, 255),

If( sum([Males Planted]) >100 and sum([Males Planted]) <=1000,RGB(235, 235, 224),

If( sum([Males Planted])>1000 and sum([Males Planted]) <=2000,RGB(217, 65, 103), RGB(255, 191, 0) )



Is possible to get the same colors by using pick and match functions ?

If yes please help me here



Labels (2)
2 Replies

You probably could if you wanted to using a different if() structure or mathematical functions, but I don't really see any advantage to doing that.

Note that your conditions are more complex than necessary - for each condition except the first you can remove the first sum part, since by definition each brand would not be entered unless the previous branch was evaluated to false (so if we reach the second branch, our sum will always be bigger than 100 and there's no need to check that again).

Partner - Creator III
Partner - Creator III

Hello @Buchannagari ,

I would strongly suggest to make this statement more easy and more dynamic by calculating the buckets in the loadscript and adding the buckets as a dimension table where you could define these colours.


// Setting up the buckets
LOAD * INLINE [Key, From, To
1, 0,100
2, 101,1000
3, 1001, 2000
4, 2001, 99999999999]

// Setting up the bucket description
LOAD * INLINE [Key| Description| Color
1| 0-100| RGB(115, 150, 255)
2| 100-1000| RGB(235, 235, 224)
3| 1001-2000| RGB(217, 65, 103)
4| >=2000|RGB(255, 191, 0) 
] (delimiter is '|');

// Loading the source
LOAD Field,[Males Planted]
LOAD * Inline [Field, Males Planted
1, 300
2, 400
3, 1000
4, 2345
5, 100
6, 50
7, 1020
8, 2999

// Matching the Males Planted with the buckettable
left join (Source)
IntervalMatch([Males Planted])
LOAD From as From_tmp
,	 To as To_tmp
Resident Bucket
// Joining the key so we can use the description as dimension
left join (Source)
LOAD From as From_tmp 
,	 To  as To_tmp
,	 Key
Resident Bucket
drop fields From_tmp
,			To_tmp
from Source;






Kind regards


If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.