Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to create composite key

i have been working on making a data model for the northwind database. i am stuck at a point of removing synthetic keys. the thing is i don't know exactly about the composite key. can anyone please tell how to apply composite key and what should be its syntax?

1 Solution

Accepted Solutions
Not applicable
Author

Its nothing but Combining the common fields which makes synthetic keys

Ex:

Tab1:

Load

Country,

Region,

Sales,

Products

From --------------;

Tab2:

Load

Country,

Region,

Customer,

Outstanding

From ---------------;

If you load these 2 tables directly your script contains synthetic keys (Country and Region-- common in both tables)

Sol:

Tab1:

Country&'-'&Region as Key    //Composite key

Sales

Products

From --------------;

Tab2:

Country&'-'&Region as Key   //Composite key

Customer,

Outstanding

From --------------;

View solution in original post

18 Replies
its_anandrjs

And more details like below

Eg:-

FieldName1 &'|'& Fielname2 as Composite_key

autonumber( Region&Year&Month, 'Ctr1' ) as Key

autonumberhash128 ( Region, Year, Month )

There are many different scenarios for synthetic keys

1. If table columns has same different fields then concatenate those tables

Eg:

Tab1:

Load Field1,Field2 from location;

Concatenate

Tab2:

Load Field1,Field2 from location;

or they will auto concatenate

2. The below table is join with Key field or they associate with each other

Tab1:

Load

Field1&'|'&Field2 as Key,

Field1,Field2,FieldA from location;

Tab2:

Field1&'|'&Field2 as Key,

Load Field1,Field2,FieldB from location;

3.Qualify the table

Qualify *;

Tab1:

Load Field1,Field2 from location;

Tab2:

Load Field1,Field2 from location;

If using Qualify the field name of table after loading comes like

Tab1.Field1,Tab1.Field2

and

Tab2,Field1,Tab2.Field2

Hope this helps

maxgro
MVP
MVP

Field1 & '-' & Field2 & '-' & Field3 as Key

very good post about synthetic key, read it if you have time

Re: Should We Stop Worrying and Love the Synthetic Key?

also look in the help at

autonumber

autonumberhash.......

tresesco
MVP
MVP

Try like below:

Load

          Field1 & Field2 as Key,

         AutoNumber(Field1 & Field2) as Key2  

Form <>;

Note: Key2 is the better way to use in terms of performance.

Not applicable
Author

Autonumber(Field1,Field2....)

This will created distinct number for the combination.

After creating the key you can drop the common fields from any one of the table to remove synthetic keys and thereby associating with the newly created key.

Thanks,

Prabhu

its_anandrjs

On in detail you can use

FieldName1 &'|'& Fielname2 as Composite_key


autonumber( Region&Year&Month ) as Key

autonumber( Region&Year&Month, 'Ctr1' ) as Key

autonumberhash128 ( Region, Year, Month )

Hope this helps

MK_QSL
MVP
MVP

You can remove synthetic key by

1) Using Qualify function for any table load

2) Renaming field names having same name 

     i.e. Address as Customer Address

          Address as Supplier Address

3) Concatenate tables

4) Create a link table by using composite key

    i.e. ID & ' '& Address as Key

We can help you better way if you can share your apps or create a sample file and upload it.

Not applicable
Author

Its nothing but Combining the common fields which makes synthetic keys

Ex:

Tab1:

Load

Country,

Region,

Sales,

Products

From --------------;

Tab2:

Load

Country,

Region,

Customer,

Outstanding

From ---------------;

If you load these 2 tables directly your script contains synthetic keys (Country and Region-- common in both tables)

Sol:

Tab1:

Country&'-'&Region as Key    //Composite key

Sales

Products

From --------------;

Tab2:

Country&'-'&Region as Key   //Composite key

Customer,

Outstanding

From --------------;

arjunkrishnan
Partner - Creator II
Partner - Creator II

Create Composite Key Like That.It's Very Useful to Avoid Synthetic Key In Data Model

Field1 & '-' & Field2 & '-' & Field3 as Key

muniyandi
Creator III
Creator III

Hi Ravi,

i understood the script  and have one doubt in your script .

Now user want to select the country and region column.what will do ?

Regards,

Muni.