topic Help with Composite key in QlikView

Help with Composite key

don_qlikview — Mon, 22 Sep 2014 21:11:26 GMT

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

Re: Help with Composite key

maxgro — Mon, 22 Sep 2014 22:30:02 GMT

see attachment

Re: Help with Composite key

don_qlikview — Tue, 23 Sep 2014 01:53:04 GMT

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

Re: Help with Composite key

JonnyPoole — Tue, 23 Sep 2014 03:06:49 GMT

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)

Re: Help with Composite key

maxgro — Tue, 23 Sep 2014 09:12:42 GMT

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

ID A,B

UID U1, U2

Re: Help with Composite key

don_qlikview — Tue, 23 Sep 2014 13:40:05 GMT

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