Synthetic Keys

[Karineyumi]

hii,

I need to create a key with 5 fields in comun but i need to keep those fields on both tables when loading.

Saleskit:
LOAD
"Year",
"Sales Country",
"Sales Manager",
"Sales rep",
"Sales Role",
"LATAM Lara BP code",
"Master Customer Name",
"Customer name",
Direction,
"Vol. Budget (Teus)",
Segmentation,
Category,
"Month",
"Week"
FROM [lib://External source files/saleskit.qvd]
(qvd);

Database:
LOAD
Service,
Voyage,
Vessel,
Booking,
BL,
"Year",
"Month",
"Week",
"LATAM Lara BP code",
Direction 
FROM [lib://External source files/export.qvd]
(qvd);

They key needs to be with the fiels ("Year"&"Month"&"Week"&"LATAM Lara BP code"&Direction).

The problem is that if i keep all the 5 fields on both table the Qlik will create another synthetic key automaticaly, which i cannot use. 

and i cannot not load all the 5 fields from both tables, because i need a "full outer join".

thankss, Karine. 

[WaltShpuntoff]

The best solution is going to depend on how you plan to use the data. Assuming you plan on selecting the same dimension and have it impact both tables

Option 1:

Just concatenate the second table to the first.

Option 2:

If for some reason you do not like Option 1, then you can move all of the common fields to a link table and create a unique id for each table. RowNo() as %KeyName is very reliable.

That would be something like this:

SalesKit:
Load RowNo() as %SalesKitID, * from ....

Database:
Load RowNo() as %SalesDBID, * from ....

LinkTable:
noconcatenate
Load 
"Year",
"Month",
"Week",
"LATAM Lara BP code",
Direction,
%SalesKitID
resident SalesKit;

Drop Fields "Year", "Month", "Week", "LATAM Lara BP code" from SalesKit;

Concatenate (LinkTable)
Load
"Year",
"Month",
"Week",
"LATAM Lara BP code",
Direction,
%SalesDBID
Resident Database;

Drop Fields "Year", "Month", "Week", "LATAM Lara BP code" from Database;

[Lisa_P · Accepted solution]

the best way to do this is to create your own link table to host the fields and relate back to original tables with composite keys.

These are the steps:

1. Rename common dimensions to unique names to break associations.

2. Create a composite key in each table with the correct combination of these fields.

3. Build a link table to host the original field values, associated back to the original tables with the composite key.

Saleskit:

Load *,

SalesYear & '|' & SalesMonth & '|' & SalesWeek & '|' & SalesLATAMLaraBPCode & '|' & SalesDirection as YMWBPD_Key;

LOAD

"Year" as SalesYear,

"Sales Country",

"Sales Manager",

"Sales rep",

"Sales Role",

"LATAM Lara BP code" as SalesLATAMLaraBPcode,

"Master Customer Name",

"Customer name",

Direction as SalesDirection,

"Vol. Budget (Teus)",

Segmentation,

Category,

"Month" as SalesMonth,

"Week" as SalesWeek

FROM [lib://External source files/saleskit.qvd]

(qvd);

Database:

Load *,

DataYear & '|' & DataMonth & '|' & DataWeek & '|' & DataLATAMLaraBPCode & '|' & DataDirection as YMWBPD_Key;

LOAD

Service,

Voyage,

Vessel,

Booking,

BL,

"Year" as DataYear,

"Month" as DataMonth,

"Week" as DataWeek,

"LATAM Lara BP code" as DataLATAMLaraBPcode,

Direction as DataDirection

FROM [lib://External source files/export.qvd]

(qvd);

//  Then build link table

LinkTable:

Load Distinct   // from SalesKit

YMWBPD_Key,

SalesYear  as Year,

SalesMonth  as Month,

SalesWeek as Week,

SalesLATAMLaraBPCode as [LATAM Lara BP code],

SalesDirection as Direction,

'SalesKit' as FromTable

Resident SalesKit;

Concatenate(LinkTable)

Load Distinct   // from Database

YMWBPD_Key,

DataYear  as Year,

DataMonth  as Month,

DataWeek as Week,

DataLATAMLaraBPCode as [LATAM Lara BP code],

DataDirection as Direction,

'Database' as FromTable

Resident Database;

// Drop un-needed fields

Drop fields DataYear,DataMonth , DataWeek , DataLATAMLaraBPCode , DataDirection,
SalesYear , SalesMonth , SalesWeek , SalesLATAMLaraBPCode , SalesDirection;

[Karineyumi]

Thank you Lisa!

Amazing! It worked with the Linktables 🙂