Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bjdouglas
Contributor II
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
Creator III
Creator III

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 😉