Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
bjdouglas
New Contributor II

Wildcard Lookup between two tables

I have a wildcarded lookup table that I want to apply to the data coming in from another table. 

Field 1Field 2Field 3Category
A10CGreen
A10*Orange
A37ABlue
A**Purple
C*JPink
J**Red
***Black

 

The table should be read top-down, left to right. * means all other values or all values if the row above it doesn't have a specific value in it. Field 1 has a range of values from A-Z. Field 2 has a range of values from 0-9, Field 3 has a range of values from A-Z.

The result I'm looking for is the following in the data I'm applying this mapping table to. 

Field 1Field 2Field 3Category
A44JPurple
A10CGreen
A10XOrange
B45DBlack
C1KBlack
J11YRed
C1JPink

 

Can I use ApplyMap or something like it to accomplish this? 

Labels (3)
1 Reply
DavidŠtorek
Contributor III

Re: Wildcard Lookup between two tables

Hi,

the way you want to use it it is not possible. You have to use some kind of If statement for your example it could be sometling like this:

If(Field1='A',
	If(Field2=10,If(Field3='C','Green','Orange'),If(Field2=37,If(Field3='A','Blue','Purple'),'Purple')),
		If(Field1='C',If(Field3='J','Pink','Black'),If(Field1='J','Red','Black'))) as Category

Hope this helps 😉