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

Assign load script applymap values to variable for chart colors

lbunnell — Wed, 25 Nov 2020 16:16:04 GMT

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

Load the color definitions into a mapping table:
ProductColors:
Mapping Load [Account Type], Rgb(Red,Green,Blue) as AccountTypeColor From ChartColors
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 Type	Color
Savings	vSTDarkBlue
Checking	vSTDarkOrange
Investment	vSTMediumBlue
Money Market	vSTLightBlue
...	...

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

sunny_talwar — Thu, 31 May 2018 15:30:12 GMT

See if this helps

Maintaining Color of the Dimensions in QlikView – Learn QlikView

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

lbunnell — Thu, 31 May 2018 23:13:04 GMT

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

sunny_talwar — Fri, 01 Jun 2018 00:11:47 GMT

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

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

marcus_sommer — Fri, 01 Jun 2018 09:10:09 GMT

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

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

lbunnell — Fri, 01 Jun 2018 10:17:37 GMT

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.

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

marcus_sommer — Fri, 01 Jun 2018 10:40:42 GMT

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

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

lbunnell — Fri, 01 Jun 2018 12:46:55 GMT

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)

)