Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Mapping on multiple fields

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.

Tags (3)
5 Replies

Re: Mapping on multiple fields

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

Not applicable

Re: Re: Mapping on multiple fields

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

Re: Re: Mapping on multiple fields

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

Not applicable

Re: Re: Mapping on multiple fields

I think that will work. Thank you.

Re: Re: Mapping on multiple fields

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

Community Browser