Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
lbunnell
Contributor

Assign load script applymap values to variable for chart colors

I would like to use the instructions provided here with a slight variation in that I'd like to use a variable for the chart background color based on the value like this:

Applymap('ChartColors', [Account Type] , lightgray()) as AccountTypeColor

However when I set the Background color in the chart expression to

$(AccountTypeColor) the colors of the chart do not change. Is there a way to accomplish this using a field value from table as a variable in the design layer?

Colors in charts

Instead of this

  1. Load the color definitions into a mapping table:
       ProductColors:
       Mapping Load [Account Type], Rgb(Red,Green,Blue) as AccountTypeColor From ChartColors
  2. Use this mapping table when loading the products table, creating a new field for the product color:
       Applymap('ChartColors', [Account Type] , lightgray()) as AccountTypeColor

I've created custom colors include file like the following:

SET vLightOrange   = RGB(252,213,180);

SET vLighterOrange = RGB(253,233,217);

SET vDarkerOrange  = RGB(247,150,70);

SET vPurple    = RGB(105,84,150);

...

along with the following in the load script:

ChartColors:
Mapping LOAD [Account Type],
Color
FROM

(
ooxml, embedded labels, table is Sheet1);

which looks like this

 

Account TypeColor
SavingsvSTDarkBlue
CheckingvSTDarkOrange
InvestmentvSTMediumBlue
Money MarketvSTLightBlue
......
1 Solution

Accepted Solutions
lbunnell
Contributor

Re: Assign load script applymap values to variable for chart colors

I found a solution from Stefan Wuhl:

Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

=pick( match( VariableName, 'VK1_ntf','VK1_ntf_int','VK2_ntf','VK2_ntf_int'),

$(VK1_ntf),

$(VK1_ntf_int),

$(VK2_ntf),

$(VK2_ntf_int)

)

7 Replies

Re: Assign load script applymap values to variable for chart colors

lbunnell
Contributor

Re: Assign load script applymap values to variable for chart colors

That's close, but I was looking to set the colors to a variable and have the Account/Color table contain the variable $(vDarkBlue) name with the CustomColors include file holding the RGB values. Maybe that's not possible?

Re: Assign load script applymap values to variable for chart colors

Would you be able to share a sample to see what exactly are you looking to get?

MVP & Luminary
MVP & Luminary

Re: Assign load script applymap values to variable for chart colors

It's not complete clear for me how do you tried to apply the colors with the mapping and how the variables are later called. But I assume that you don't assigned the variable-value per mapping to the dimension (if it would be logically quite the same like the suggestion from HIC in the above mentioned link) else the variable-name as string. This of course will lead to the problem that it is just a string until you used a $-sign expansion - but this results in a new adhoc-variable and is not working on a dimension-level.

Beside this I don't see any added value to implement a color-logic on a variable-basis compared to a table- and field-value approach. If you ever get it to work it will be more expensive and complex as the suggestion from HIC - why not just copying / adapting this approach?

- Marcus

lbunnell
Contributor

Re: Assign load script applymap values to variable for chart colors

Yes, I could use the other approach, but I already had the custom color include file with numerous RGB values created and I want to know, for other future use cases, whether it was possible to convert a field value like this one in Henric's example from an ApplyMap created value to a create an ad hoc variable which I am attempting to use as the Chart Expression background color per the example.

I've tried setting a variable in Variable Overview like vAccountTypeColor = FieldValue('AccountTypeColor') then applying $(vAccountTypeColor) to the Background of the Chart Expression.

Also tried simply setting the Chart Expression directly to =$('AccountTypeColor'), however neither of these approaches seem to work.

Basically, I'd like to know if there is a way to use the values from a field in the load script to create a dynamic variable.

MVP & Luminary
MVP & Luminary

Re: Assign load script applymap values to variable for chart colors

I don't want to say that's not possible to implement a variable-solution but I doubt that it would make much sense to go this (probably terrible) way.

What speaks against the approach to use the variable-file to create a table?

- Marcus

lbunnell
Contributor

Re: Assign load script applymap values to variable for chart colors

I found a solution from Stefan Wuhl:

Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

=pick( match( VariableName, 'VK1_ntf','VK1_ntf_int','VK2_ntf','VK2_ntf_int'),

$(VK1_ntf),

$(VK1_ntf_int),

$(VK2_ntf),

$(VK2_ntf_int)

)