Still no VLookup?

[TimmyCNHi]

The goal is simple: access a table, find a value of one column and match it with the value of another column in the same row. The road to get there in QS, however, doesn't seem to be so simple. Or is it?

Let's take this load script:

LOAD
    Type,
    State
FROM [lib://FRUIT_MANAGEMENT/FruitMan.xlsx]
(ooxml, embedded labels, table is "Basket");

Which generates the following table:

Type	State
Apple	Fresh
Pear	Rotten
Orange
Banana
Tomato	Fresh

I have a dashboard with a basket chart (yes, I just made that up), which looks like this:

The goal here is to colour the text boxes based on a fruit's state:
Fresh = Green (#00a708)
Rotten = Red (#ff002a)
(blank) = Blue (#0018ff)

Eg. If the apple turns rotten, its box colour should change from green to red on a data refresh.

In Excel this would be fairly simple:

=VLOOKUP(A2;A1:B6;2;FALSE)

Which would return: Fresh

So to make the colouring work it would need something like:

=IF(VLOOKUP(A2;A1:B6;2;FALSE)="Fresh";"#00a708";IF(VLOOKUP(A2;A1:B6;2;FALSE)="Rotten";"#ff002a";"#0018ff"))

Say I have the same table as loaded above, how could I make these colours work in my Qlik Sense basket chart visualisation? I could be using a Button and I'm trying to set the Background under Appearance 'By expression'.

Thank you for your time!

[jbhappysocks]

Hi

pick(match(State,'Fresh','Rotten',''),green(),red(),blue())   creates this: 

Not as fancy as you basket chart, but you get the idea. 

Your hex codes needs to be converted to rgb()-codes if you want the exact colors.

[Or · Accepted solution]

Seems like a fairly standard mapping load scenario (though there's multiple other ways to achieve the same thing)

States:

Mapping Load * INLINE [

StateName, Color

Fresh, Green

Rotten, Red];

LOAD
Type,
State,

ApplyMap('States',State, 'Blue') as StateColor

FROM [lib://FRUIT_MANAGEMENT/FruitMan.xlsx]
(ooxml, embedded labels, table is "Basket");

[TimmyCNHi]

Hi

Thanks! For a table this indeed works but for a single button this doesn't seem to do anything. How do I make it clear to QS that a button should be assigned to 'Apple'?

[TimmyCNHi]

Hi

I'm a bit lost on this 'mapping'. Why would I need to map something while the table is right there already?

I do understand that this creates another table linking the current Types and States to a new column named 'StateColor' (which I could also achieve in the Excel table it's linked to) but this also doesn't make it clear to my button that it should behave as an Apple (and thus turn green as it's 'Fresh').

[jbhappysocks]

Sorry, I must learn to read the whole question, didn't see the part about a button.

I would go with the mapping suggestion from Or instead then.

[Or]

For the same reason you need VLOOKUP (or the significantly-better index/match combination) - you have two tables, and you want to match them based on common values.

In Qlik, you use mapping loads when you want to look up a value based on an existing field, while also filling in the cases where the value is missing. That's exactly what we have here - you want to fill in a value for color based on the state, and also fill in blue for the missing values. If you didn't have missing values, you could just use a regular JOIN on the state vs. color table to the main table.

[TimmyCNHi]

Yes but I don't want to do this in a table, it needs to be transposed to several separate buttons (or any shapes).

With this mapping I managed to use an easier way to colour the background of the 'State' column items in a table by setting 'StateColor' as 'Background color expression'.

I can set the background colour of a button to 'StateColor' but the button doesn't understand what it is. I have the feeling I'm on the right track though! Does its 'Label' need a specific syntax or can filling in a certain syntax in the 'Calculation condition' or 'Enable chart' solve this?

[Or]

I'm not sure what 'understand' would entail in this context. You can use this field to color the button, as you mentioned. You could also use it within formulas for the button or its actions. Could you explain exactly what it is you're trying to achieve?

[TimmyCNHi]

How to set the properties of this button:

I tried setting 'Background color' as a 'StateColor' expression and a bunch of combinations with 'Label' and 'Enable chart' (also threw Jbhappysocks' solution in there) but it doesn't seem to respond to anything.