topic Re: Synthetic key Resolution in QlikView

Synthetic key Resolution

arusanah — Wed, 08 Apr 2015 01:50:15 GMT

Hi ,

I Have two Tables Table A & Table B . Table A fetches data from .xlsx and Table from SAP. I have to create link between two . I tried creating link table by fectching data from both tables as shown in link table.

By doing so I get syn key in my data model (%KEY_PAYER & Payer.Country) . I need to resolve that. But not able to find any solution.

Pls help !!!

Table A	Link Table	Table B
Payer Row() Country Country Code MatchField	Tab: Load: wildMatch(payer.Name,matchfield,$vMap as PayerRow() , %KEY_PAYER, Payer.Country Resident TableB; Inner join(tab) Load Payer Row() Country Code as Payer.Country Resident Table A;	%KEY_PAYER Country Payer.CountryCode Payer.Name

Table A

Link Table

Table B

Payer Row()

Country

Country Code

MatchField

Tab:

Load:

wildMatch(payer.Name,matchfield,$vMap as PayerRow() ,

%KEY_PAYER,

Payer.Country

Resident TableB;

Inner join(tab)

Load

Payer Row()

Country Code as Payer.Country

Resident Table A;

%KEY_PAYER

Country
Payer.CountryCode
Payer.Name

Re: Synthetic key Resolution

Anonymous — Wed, 08 Apr 2015 02:23:06 GMT

Try (customize) below code in place of your link table code

Tab:

Load
wildMatch (payer.Name,matchfield,$vMap) as [Payer Row],

%KEY_PAYER

Resident TableB;

Inner join(Tab)

Load

Payer Row() as [Payer Row],

Country Code as [Payer Country]

Resident Table A;

Re: Synthetic key Resolution

ramoncova06 — Wed, 08 Apr 2015 03:27:07 GMT

you can create a composite field by using %KEY_PAYER &'_'& Payer.Country

I modified your script since normally the link tables should be at the end of the script and not before the tables they are reading the data from

TableA:

Payer Row(),

Country,

Country Code

MatchField

from .....

TableB:

%KEY_PAYER,

Country,

Payer.Country,

Payer.Name,

%KEY_PAYER&'_'&Payer.Country as %K_Composite

from ...

Tab:

Load:

wildMatch(payer.Name,matchfield,$vMap as PayerRow() ,

%K_Composite

Payer.Country

Resident TableB;

Inner join(Tab)

Load

Payer Row(),

Country Code as Payer.Country

Resident Table A;

drop field Payer.Country from Tab;