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 🙂

[Karineyumi]

Hi Lisa, thank you so much for helping me with this.

I have another problem, and i would love if you could help me again.

So same thing the subject on this forum.

I created the link tables for Saleskit and Database as you suggest and it works well.

but now, i have another table called BudgetAlloc:

BudgetAlloc:
LOAD
"Year" as RMYear,
Cluster,
Country as RMCountry,
SERVICE as RMService,
ZOD as RMZod,
"Reefer flag" as RMreeferflag,
Direction as RMDirection,
"Target/teu/week",
"Month" as RMMonth,
"Week" as RMWeek,
AutoNumber("Year" & '|' & "Month" & '|' & "Week" & '|' & Country & '|' & Direction & '|' & SERVICE & '|' & ZOD & "Reefer flag") as YMWCD_Key
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/BudgetAlloc.qvd]
(qvd);

which the I need to link with the Database:

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);

but since i already have the Linktable from Saleskit with the "Year", "Month", "Week" and "Direction" which would be a comum field between the 3 tables (Database, Saleskit, BudgetAlloc) the system creates synthetic keys, which i cannot have.

so to summarize, I need to continue with my 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;

and create a new Linktable just between Database and BudgetAlloc, but still keeping all the fields from "BudgetAlloc".

thank youuuu

[Lisa_P]

I can't see any relationship with ZOD and Reefer flag to Database, can you please advise. Also, is there anything that is similar to the DataLATAMLaraBPCode in Budget ?

[Karineyumi]

Hi Lisa, good morning!

Yes, i have those fields on my Database as well.

I don`t have the DataLATAMLaraBPCode in the Budget, that budget i need to connect with my Country, Year, Month, Week, Direction, ZOD, Service and Reefer flag. 

We have the following tables:

BudgetAlloc:
LOAD
"Year" as RMYear,
Cluster,
Country as RMCountry,
SERVICE as RMService,
ZOD as RMZod,
"Reefer flag" as RMreeferflag,
Direction as RMDirection,
"Target/teu/week",
"Month" as RMMonth,
"Week" as RMWeek,
AutoNumber("Year" & '|' & "Month" & '|' & "Week" & '|' & Country & '|' & Direction & '|' & SERVICE & '|' & ZOD & "Reefer flag") as YMWCD_Key
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/BudgetAlloc.qvd]
(qvd);

Saleskit:
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)",
"VAS Budget ($)",
"CH Budget (Teus)",
"Master BP code",
Country_Code,
"Month" as SalesMonth,
"Week" as SalesWeek,
AutoNumber("Year" & "Month" & "Week" & "LATAM Lara BP code" & Direction ) as Saleskit_Key
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/ImpChg_1_2023.qvd]
(qvd);

Database:

Load

    Service,

    Voyage,

    Vessel,

    Booking AS DATABooking,

    "SHP Name" AS "DATASHP Name",

    "DCD Name" AS "DATADCD Name",

    "DCD country" AS "DATADCD country",

    "CEE Name" AS "DATACEE Name",

    "NOT Name" AS "DATANOT Name",

    "POL ETD" AS "DATAPOL ETD",

    POO AS DATAPOO,

    POL AS DATAPOL,

    "POL Name" as DATAPOLName,

    POD AS DATAPOD,

    "POD Name" as DATAPODName,

    "Delivery (FPD)" AS "DATADelivery (FPD)",

    "Commodity description" AS "DATACommodity description", 

    "Commodity code" AS "DATACommodity code",

    Package,

    "Reefer Flag" AS DATAREEFER,

    "Booking Party Name" AS "DATABooking Party Name",

    "POD ETA" AS "DATAPOD ETA",

    "Ultimate Country" as DataUltimateCountry,

    "Service Adjusted" AS DATASERVICE,

    "Teus Incl. LS",

    "SQ/SVC" AS "DATASQ/SVC",

    "Year" as DataYear,

    "Month" as DataMonth,

    "Week" as DataWeek,

    "LATAM Lara BP code" as DataLATAMLaraBPcode,

    Direction as DataDirection,

    "Ultimate Zone, ZOD(for Exp)/ZOL (for Imp)" as DATAZOD,

    AutoNumber(Year&"Reefer Flag"&Month&Direction&"Ultimate Country") as CTS_Key,

    AutoNumber("Year" &"Month" & "Week" & "LATAM Lara BP code"& Direction ) as Saleskit_Key,

    AutoNumber(Booking&Direction&Year&Month&Week) as VAS_Key,

    Month(Month) as MonthNum,

//     AutoNumber("Ultimate Country"&Direction&"Year"&Month) as RoapMap_Key,

    "Ultimate MB Zone",

    "Ultimate Port",

AutoNumber("Year" & '|' & "Month" & '|' & "Week" & '|' & "Ultimate Country" & '|' & Direction & '|' & "Service Adjusted" & '|' & "Ultimate Zone, ZOD(for Exp)/ZOL (for Imp)" & "Reefer Flag") as YMWCD_Key

FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/ExpChg_1_2023.qvd]

(qvd)

Where match("Booking status", 1,20,30,40,50,60,70, null());

thank you!