Lookup into multiple tables

[qlikwiz123]

I have three tables

Source:                                                        

Name	ID	Region
A	1	ASIA
B	4	ASIA
C	5	NA
D	7	EMEA
E	9	ASIA
F	2	ASIA
G	5	EMEA
H	8	EMEA
I		NA
J		ASIA
K		NA
L		ASIA
M		EMEA
N		EMEA

Category:

Name	CategoryID
A	4
B	5
C	9
D	3
E	6
F	1
G	0
H	8
I	2
J	7
K	10
L	23
M	65
N	12
O	66
P	54
Q	35
R	87
S	98
T	67
U	23
V	11
W	70
X	30
Y	29
Z	36

Property:

CategoryID	ID
45	1
96	4
91	5
88	7
62	9
99	2
19	5
55	8
2	71
7	73
10	72
23	75
65	49
12	59

I need to fill the values for 'ID' in table 'Source' by looking into 'Category' and 'Property Tables'

In plain English, I should use 'Name' from Source table and look into Category table, use the CategoryID to get relevant 'ID' values from Property table and populate the Source Table as below:

Result Table: Source:

Name	ID	Region
A	1	ASIA
B	4	ASIA
C	5	NA
D	7	EMEA
E	9	ASIA
F	2	ASIA
G	5	EMEA
H	8	EMEA
I	71	NA
J	73	ASIA
K	72	NA
L	75	ASIA
M	49	EMEA
N	59	EMEA

Help to achieve this is greatly appreciated as I am trying ApplyMap, Left Join etc for days now but not able to do this 🙂

[kaushiknsolanki · Accepted solution]

Try this.

Category:
Mapping Load * inline [
Name, CategoryID
A, 4
B, 5
C, 9
D, 3
E, 6
F, 1
G, 0
H, 8
I, 2
J, 7
K, 10
L, 23
M, 65
N, 12
O, 66
P, 54
Q, 35
R, 87
S, 98
T, 67
U, 23
V, 11
W, 70
X, 30
Y, 29
Z, 36
];

Property:
Mapping Load * inline [
CategoryID, ID
45, 1
96, 4
91, 5
88, 7
62, 9
99, 2
19, 5
55, 8
2, 71
7, 73
10, 72
23, 75
65, 49
12, 59
];

Source:
Load *,If(ID = '' or isnull(ID),Applymap('Property',ApplyMap('Category',Name)),ID) as NewID inline [
Name, ID, Region
A, 1, ASIA
B, 4, ASIA
C, 5, NA
D, 7, EMEA
E, 9, ASIA
F, 2, ASIA
G, 5, EMEA
H, 8, EMEA
I, , NA
J, , ASIA
K, , NA
L, , ASIA
M, , EMEA
N, , EMEA
];

[qlikwiz123]

@kaushiknsolanki  Thank you so much, this is wonderful