Mapping on multiple fields

[#1]

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.

[MK_QSL]

Kindly provide few line sample data along with your final requirements and logic behind that...

[#3]

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.
*/

[MK_QSL]

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..

[#5]

I think that will work. Thank you.

[MK_QSL]

If this will work for you,  kindly close the thread by selecting correct answer