Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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
];
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
];
@kaushiknsolanki Thank you so much, this is wonderful