create composite key based on values from 2 tables

[PeterHenskens]

hi,

i've been searching the community for a couple of days, seems like i cant figure out how to solve this one.

i have 7 tables 

Main table

DataDetails:
LOAD Id,
[Delivery Postal Code], 
[Forwarder Track],

left join
LOAD [Delivery Postal Code],
Zone

To define the weight clas

GewichtKlassen:
LOAD Min,
Max,
Gewicht_Klasse

inner join IntervalMatch (LAN_Weight)

load Min,
Max
Resident
GewichtKlassen;

Rates based in Zone and weight

Landmark_Prices:
LOAD Price_ID,
Price as LA_Price

now I would like to make a key in the table Data details something like

trim (Zone&'-'&Gewicht_Klasse) as Price_ID

then i would be able to link the table DataDetails with Landmark_Prices

but when doing this i get an error that "Gewicht_Klasse" cannot be found (probably because its in another table?)

[jensmunnichs]

Yeah I don't think you can do that when they're not in the same table (how would it know which weight class belongs to which zone?).

So you're going to have to get both fields to be in the same table. One option would be to join LAN_Weight to data details on customer ref (from the invoices table), the join the Gewicht_Klasse from the GewichtKlassen table. Then drop the customer ref field if you want.

Might be a more efficient way to incorporate this into your existing script but it's hard to tell without seeing the full script.

[PeterHenskens]

Jens,

thank you for the responce.

i've tried to join "Gewicht_Klasse" into Landmark_Invoices.

but could not do this (probably just because i dont know how...) 

because "Gewicht_Klasse"  comes from an inner join intervalMatch from Gewichtklassen

do you know how to left join this into Landmark_Invoices?

Landmark_Invoices:
LOAD [Customer Ref],
[Processed/Returned Date] as LAN_Invoice_Date,
[Weight (lb)] as LAN_Weight,
[Base Charge],
[Additional Charges],
[Total Charge]
FROM

GewichtKlassen:
LOAD Min,
Max,
Gewicht_Klasse
FROM

// i've tried (Red)  but failed...
left join
inner join IntervalMatch (LAN_Weight)

load Min,
Max
Resident
GewichtKlassen;

[jensmunnichs · Accepted solution]

Again this might not be the most efficient solution, but I would just go with the script you posted in your OP, then add:

Left join (DataDetails)
LOAD [Customer Ref], LAN_Weight
Resident Landmark_Invoices;

Left join (DataDetails)
LOAD LAN_Weight, Gewicht_Klasse
Resident GewichtKlassen;

Drop field LAN_Weight from DataDetails;

Left join (DataDetails)
LOAD [Forwarder Ref], trim (Zone&'-'&Gewicht_Klasse) as Price_ID
Resident DataDetails;

I think that should work, if not let me know.

[PeterHenskens]

hey, brilliant !

this works perfecty.

i just needed to add 1 row under the last join:

drop field Gewicht_Klasse from GewichtKlassen;

Else i would have created a circle, but with this line included it works perfrctly.

[jensmunnichs]

You are right, I forgot about that. Glad it's working 🙂

You could also drop Gewicht_Klasse from your DataDetails table after you've made your key, since then you'd be able to keep it in the GewichtKlassen table, which might make a little bit more sense than just having the brackets in that table but no weight classes.