Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have two data files and I want to create a composite key with three fields that comes from two different tables. Below are the sample fields from two tables
Table1:
Year,
Code,
UID,
field1,
field2,
field3
Table2:
UID,
ID
field4,
field5
Now, I want to create a composite key with ID & Year & Code as CompositeKey.
How can i do this, can anyone please help?
Thanks,
Don
see attachment
see attachment
Hi Massimo,
Thanks for the sample application but I want to know how can I create the key with ID & Year & Code? (In your code you have done it with UID & Year & Code)
Thanks,
Don
You can use autonumber() function to do this:
add the autonumber expression to both table loads and pass it each field in the composite key seperated by an '&'. The 2nd arguement 'Key' is needed if the field names within the key are not identical in both tables.
ie:
load
field1,
field2,
field3,
autonumber( ID&Year&Code,'Key') as Key
from <source1>
load
field4,
field5
autonumber( ID&Year&Code,'Key') as Key
from <source2>
...note that you actually need the 3 fields in BOTH tables to do this kind of join BUT with autonumber the fields don't have to be named the same in both tables. leave out the individual fields from the load that share the same name in both tables to avoid extra unneeded joins (or rename the fields)
my composite key is ID-Year-Code (A-200-1)
ID A,B
UID U1, U2
Sorry, I didn't see that and Thanks very much this has worked for me...