topic Re: how to create composite key in QlikView

how to create composite key

Wed, 02 Apr 2014 10:30:53 GMT

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?

Re: how to create composite key

its_anandrjs — Wed, 02 Apr 2014 10:33:37 GMT

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

Re: how to create composite key

maxgro — Wed, 02 Apr 2014 10:34:01 GMT

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.......

Re: how to create composite key

tresB — Wed, 02 Apr 2014 10:34:11 GMT

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.

Re: how to create composite key

Wed, 02 Apr 2014 10:35:06 GMT

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

Re: how to create composite key

its_anandrjs — Wed, 02 Apr 2014 10:35:26 GMT

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

Re: how to create composite key

MK_QSL — Wed, 02 Apr 2014 10:35:53 GMT

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.

Re: how to create composite key

Wed, 02 Apr 2014 10:37:57 GMT

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 --------------;

Re: how to create composite key

arjunkrishnan — Wed, 02 Apr 2014 10:47:10 GMT

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

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

Re: how to create composite key

muniyandi — Wed, 02 Apr 2014 11:19:46 GMT

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.

Re: how to create composite key

its_anandrjs — Wed, 02 Apr 2014 11:30:09 GMT

Then load a script like

Sol:

Tab1:

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

Country as Tab1_Country,

Region as Tab1_Region

Sales,

Products

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

Tab2:

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

Country as Tab2_Country,

Region as Tab2_Region

Customer,

Outstanding

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

Rename this two fields in the table

Re: Re: how to create composite key

Wed, 02 Apr 2014 11:57:30 GMT

You can rename those fields

ex:

In Tab1:

Country as Tab1_Country

Region as Tab1_Region

In Tab2:

Country as Tab2_Country

Region as Tab2_Region

You can keep Country and Region as it is from any one table.

Now all your fields are unique except Key field(composite key) and relation ship exists between your 2 tables based on this

Re: how to create composite key

muniyandi — Wed, 02 Apr 2014 12:10:00 GMT

Hi,

Tab1 & Tab2 both are common field. i want select common single column in listbox.

Regards,

Muni

Re: how to create composite key

its_anandrjs — Wed, 02 Apr 2014 12:16:54 GMT

In Tab1 & Tab2 not all fields are common in above suppose they are same

Eg:-

Tab1:

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

//Country as Tab1_Country,

//Region as Tab1_Region

//Sales

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

Tab2:

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

//Country as Tab2_Country,

//Region as Tab2_Region

//Sales,

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

After loading this two tables concatenate to each other and give single table with key field

Re: how to create composite key

muniyandi — Wed, 02 Apr 2014 12:23:05 GMT

i want county,region,sales field in list.

user want select list box to filter purpose.

Re: how to create composite key

MK_QSL — Wed, 02 Apr 2014 12:39:00 GMT

Please check enclosed file having dummy data which can help to understand how synthetic key/table can be removed using Link Table approach.

UPDATE : You can check the table structure by CTRL + T

Hope this helps...

Re: how to create composite key

robert99 — Wed, 02 Apr 2014 12:51:51 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

I like synthetic keys in some circumstances. As long as I have planned to use them. For example I joined the three main tables together in a service company using a synthetic key

The call table (call_num) and field service visit table number (call_Num & FSR_Num) with the revenue charges lines (call_Num & FSR_Num & FSRL_Num)

This was tidier and the saved file was smaller than when I used a composite key

Re: how to create composite key

Thu, 03 Apr 2014 06:58:42 GMT

Hi Anand,

I have got nothing common from the table which i want to add in Data model.Can u help as how to use Composite key on that.Same data as in discussion.

Table Name:Sales Reports

Fields:ID,Display,GroupBy,Title,Filterrowsource.

Re: how to create composite key

Mahamed_Qlik — Tue, 19 Apr 2016 13:43:09 GMT

Hi ,

Is it necessary to add any separation symbol in between?

For example in your case you are using the separation symbol "-".

What if I directly create the key as -

Sol:

Tab1:

Country&Region as Key //Composite key

Sales

Products

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

Tab2:

Country&Region as Key //Composite key

Customer,

Outstanding

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

Regards,

Mahamed