topic Re: Merging data from different sources with common values in QlikView

Merging data from different sources with common values

Wed, 17 Aug 2016 14:17:52 GMT

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

Re: Merging data from different sources with common values

pgrenier — Wed, 17 Aug 2016 15:40:43 GMT

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!

Re: Merging data from different sources with common values

Anonymous — Wed, 17 Aug 2016 15:55:01 GMT

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;