Help with Composite key

[don_qlikview]

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

[maxgro · Accepted solution]

see attachment

[don_qlikview]

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

[JonnyPoole]

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)

[maxgro]

my composite key is ID-Year-Code (A-200-1)

ID      A,B

UID    U1, U2

[don_qlikview]

Sorry, I didn't see that and Thanks very much this has worked for me...