topic Re: Still no VLookup? in App Development

Still no VLookup?

TimmyCNHi — Tue, 19 Oct 2021 09:15:06 GMT

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!

Re: Still no VLookup?

jbhappysocks — Tue, 19 Oct 2021 13:49:16 GMT

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.

Re: Still no VLookup?

Or — Tue, 19 Oct 2021 14:04:40 GMT

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");

Re: Still no VLookup?

TimmyCNHi — Tue, 19 Oct 2021 14:34:07 GMT

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'?

Re: Still no VLookup?

TimmyCNHi — Tue, 19 Oct 2021 14:50:50 GMT

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').

Re: Still no VLookup?

jbhappysocks — Tue, 19 Oct 2021 14:54:22 GMT

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.

Re: Still no VLookup?

Or — Wed, 20 Oct 2021 08:02:39 GMT

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.

Re: Still no VLookup?

TimmyCNHi — Wed, 20 Oct 2021 11:24:27 GMT

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?

Re: Still no VLookup?

Or — Wed, 20 Oct 2021 11:32:28 GMT

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?

Re: Still no VLookup?

TimmyCNHi — Wed, 20 Oct 2021 12:22:05 GMT

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.

Re: Still no VLookup?

jbhappysocks — Wed, 20 Oct 2021 12:56:39 GMT

Your Button doesn't know that it is an Apple Button, you can't tie a dimension to the object, so it doesn't know what StateColor to use, you'll have to tell it. Try only({<Type = {'Apple'}>} StateColor) instead of just StateColor.

Re: Still no VLookup?

TimmyCNHi — Wed, 20 Oct 2021 13:05:10 GMT

Yes! That's the one! Thank you, you guys are amazing!