Merging data from different sources with common values

[#1]

Dear all,

Lately I have faced a serious issue during merging data from different sources with common values.

Let me show you what are the tables:

Table1:

N° xH	xH Description
xh1
xh2
xh3
xh4

Table2:

N° xL	xL Description
xl1
xl2
xl3
xl4

After a concatenation of both tables:

N° xLH	xLH Description
xh1
xh2
xh3
xh4
xl1
xl2
xl3
xl4

and there is a master table of a unique relationship between the common values:

Master Table:

N° xH	N° xL
xh1	xl2
xh2	xl1
....
....

The output of all these steps is to replace the common values in the resulting table with N° xhn::

N° xLH	xLH Description
xh1
xh2
xh3
xh4
xh2
xh1
xl3
xl4

would you please guide me on this hot topic!

You help help is mostly appreciated

[pgrenier]

Hello Yassine,

Typically, you could resolve this with the use of the ApplyMap fonction during the load of your second table. Your script could look like this:

Map_MasterTable:

Mapping LOAD [N° xL], [N° xH]

From [Master Table];

Map_xHDescritions:

Mapping LOAD [N° xH], [xH Description]

From Table1;

Results:

LOAD [N° xH] as [N° xLH], [xH Description] as [xLH Description]

From Table1;

Concatenate (Results)

LOAD ApplyMap('Map_MasterTable', [N° xL]) as [N° xLH]

      , ApplyMap('Map_xHDescritions', ApplyMap('Map_MasterTable', [N° xL]), [xL Description]) as [xLH Description]

From Table2;

Cheers!

[Anonymous]

Hi

Please find the QVW.

TABLE1:

LOAD [N° xH],

     [xH Description]

FROM

[https://community.qlik.com/thread/229434]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate

TABLE2:

LOAD [N° xL] AS  [N° xH],

     [xL Description]

FROM

[https://community.qlik.com/thread/229434]

(html, codepage is 1252, embedded labels, table is @2)

;

MAPPING:

Mapping LOAD

  [N° xH], [N° xL]

FROM

[https://community.qlik.com/thread/229434]

(html, codepage is 1252, embedded labels, table is @4);

OUTPUT:

LOAD *,

ApplyMap('MAPPING',[N° xH]) AS NEW_XH

Resident TABLE1;

DROP Table TABLE1;