topic Re: Re: Mapping on multiple fields in QlikView

Mapping on multiple fields

Thu, 18 Sep 2014 12:36:47 GMT

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.

Re: Mapping on multiple fields

MK_QSL — Thu, 18 Sep 2014 12:41:35 GMT

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

Re: Re: Mapping on multiple fields

Thu, 18 Sep 2014 13:22:18 GMT

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

MK_QSL — Thu, 18 Sep 2014 13:31:33 GMT

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

Re: Re: Mapping on multiple fields

Thu, 18 Sep 2014 19:07:21 GMT

I think that will work. Thank you.

Re: Re: Mapping on multiple fields

MK_QSL — Thu, 18 Sep 2014 20:02:52 GMT

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