Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need some scripting help. I'm familiar with applymap(), but I need something that works on multiple fields. For instance, I have a table Main with columns ID, A, B:
I need something that works to the effect of, if A = 'X' and B = 'Y' then let B = 'Z'.
I have several such "exclusions," and it would be nice to put them in an inline mapping table with columns A, B_old and B_new. In this case, the inline mapping table would have a row ('X', 'Y', 'Z').
I thought of using a left join on the inline mapping table from the Main table to create my column B, but then only mapped values will populate the new column B.
Any ideas? Thanks.
Kindly provide few line sample data along with your final requirements and logic behind that...
LOAD * INLINE [
ID, A, B
1, X, M
2, X, M
6, X, N
7, X, N
8, X, R
10, X, M
15, Y, M
16, Y, M
17, Y, M
18, Y, N
21, Y, P
22, Y, P
23, Y, P
32, Z, M
33, Z, M
34, Z, N
38, Z, P
39, Z, P
40, Z, R
41, Z, R
];
InlineMapping:
LOAD * INLINE [
A1, B1, B2
X, R, N
Z, M, N
];
/*
Use Mapping table to modify contents of Main table:
Row 1: If A = X and B = R then set B = N
Row 2: If A = Z and B = M then set B = N
In my actual project, table Main is loaded from a QVD. So this mapping can be done during or after the table load.
*/
InlineMapping:
MAPPING LOAD A1&B1 as Key, B2 INLINE
[
A1, B1, B2
X, R, N
Z, M, N
];
Final:
LOAD ID, A, B, ApplyMap('InlineMapping',A&B,B) as NewB INLINE
[
ID, A, B
1, X, M
2, X, M
6, X, N
7, X, N
8, X, R
10, X, M
15, Y, M
16, Y, M
17, Y, M
18, Y, N
21, Y, P
22, Y, P
23, Y, P
32, Z, M
33, Z, M
34, Z, N
38, Z, P
39, Z, P
40, Z, R
41, Z, R
];
I have kept both B and NewB so compare for you..
I think that will work. Thank you.
If this will work for you, kindly close the thread by selecting correct answer