topic Re: Composite Key in QlikView

Composite Key

nihhalmca — Tue, 09 Jun 2015 14:47:36 GMT

Hi All,

Which scenarios we go for 'composite key' except avoiding synthetic keys. can you give any example.

My question is both values (Year and Product) should be same in both key values to get data on selections.

For Example:

Table1:

LOAD

Year&Product AS Key,

LOAD * INLINE [

Year, Product, Sales

2011, A, 10

2012, B, 20

2013, C, 30

];

Table2:

LOAD

Year&Product AS Key,

LOAD * INLINE [

Year, Product, Rev

2011, A, 1000

2012, B, 2000

2013, X ,3000

];

Thanks

Nihhal.

Re: Composite Key

buzzy996 — Tue, 09 Jun 2015 15:09:45 GMT

ya,the result is correct!

wht's ur expecting output?

Re: Composite Key

nihhalmca — Tue, 09 Jun 2015 15:24:08 GMT

Both keys must be same. I mean there is year is common so will get data? or product has to same?

Example: 2013C from table1 and 2013X from table2.

Re: Composite Key

buzzy996 — Tue, 09 Jun 2015 15:30:16 GMT

I mean there is year is common so will get data? or product has to same? --no you can't get as per ur current keys.

if u want to get ur products based on same year,u have to modify ur keys first,because this case u key is combination of year & product.

Re: Composite Key

robert_mika — Tue, 09 Jun 2015 15:30:21 GMT

You can use

left(Data,4) as Year

in both tables to get Year value as key.

Re: Composite Key

ramoncova06 — Tue, 09 Jun 2015 15:43:06 GMT

why do you want to create a composite key ? because on the synthetic key ?

Re: Composite Key

jagan — Wed, 10 Jun 2015 02:21:09 GMT

Hi Nihhal,

In the current scenario both the year and key should match, then only those records will be joined correctly. If you just want to consider year only then join both the tables by year

Table1:

LOAD

Left(Key, 4) AS Year,

FROM Table1;

Table2:

LOAD

Left(Key, 4) AS Year,

FROM Table2;

When you execute the above script both the tables are joined by year, so you will get this row as match.

2013 xxxxx 2013

Hope this helps you.

Regards,

Jagan.

Re: Composite Key

nihhalmca — Thu, 11 Jun 2015 10:43:02 GMT

Hi Jagan,

2013C, 2013X data shows by selection of year = 2013. In the scenes both composite key total values should not be same.

For example:

Year&Product&Customer id as key (Table1)

Year&Prodcut as key (Table2)

Here when i select Year it shows data from 2 tables

if i select Product it shows data from 2 tables

if i select Cutomer id it shows data from table1 itself.

Am i right?

Nihhal

Re: Composite Key

nihhalmca — Thu, 11 Jun 2015 10:43:44 GMT

Hi Shiva,

2013C, 2013X data shows by selection of year = 2013. In the scenes both composite key total values should not be same.

For example:

Year&Product&Customer id as key (Table1)

Year&Prodcut as key (Table2)

Here when i select Year it shows data from 2 tables

if i select Product it shows data from 2 tables

if i select Cutomer id it shows data from table1 itself.

Am i right?

Nihhal

Re: Composite Key

buzzy996 — Thu, 11 Jun 2015 10:55:34 GMT

wht ever the result is displaying as per the current selections are is absolutly right!

i didn't ding customer id in table 1?whr it's?

and u can tell wht is ur expected out put here?

Re: Composite Key

nihhalmca — Thu, 11 Jun 2015 13:44:19 GMT

Thanks for writing shiva.

There is no customer id just i given example.

I have confusion which scenarios we go for 'composite key' except avoiding synthetic keys. can you give any example.

Thank you.

Nihhal.

Re: Composite Key

buzzy996 — Thu, 11 Jun 2015 14:10:36 GMT

as u said,for avoiding synthetic keys u can use composite keys as technique.

for example ur have 3 tables,

table1 having 10 fields,out of 10 fields 5 are key fields

table2 having 15 fields,out of 15 fields 7 are key fields

table3 having 20 fields,out of 20 fields 3 are key fields

but,we have to map all these tables,here some of the key fields in table1 will match with tabl2 key fields and some of the key fields in table2 will match with table3 keys fields.

so,this is the case u have to create multiple key field by combining those fields from corresponding table.

note: pls mark whichever post helps to as helpful.

Re: Composite Key

Peter_Cammaert — Thu, 11 Jun 2015 14:37:47 GMT

No, you're assuming that keys can do partial matching which they cannot. If all customer IDs have values, the keys will never match. and the two tables will be in separate universes (that is if you rename at least one set of
Product & Year fields, otherwise you'll have a syntehtic key as well).

If you want a simple solution, LEFT JOIN the second table to the first. Rows that have a Sales amount but no Rev value will have Null in the Rev field. Rows that have a Rev value but no Sales amount will have Null in the Sales field. Rows that have both will show both when you select the corresponding Year (& Product).

If you want to forcibly link with a key, create identical keys in both tables and rename at least one set of Product/Year fields. You will now have a problem with Dimension filtering because the only Product field left will only show values from one table. That's why you should extract in a second step all Product/Year pairs from both tables into a third table, together with their key values. Then drop both Product and Year fields from the original tables, as they will become Dimensions in the third table.

Peter

Re: Composite Key

pratap6699 — Thu, 11 Jun 2015 15:41:29 GMT

Table1:

LOAD

Year&Product AS Key,

LOAD * INLINE [

Year, Product, Sales

2011, A, 10

2012, B, 20

2013, C, 30

];

Table2:

LOAD

Year&Product AS Key,

LOAD * INLINE [

Year, Product, Rev

2011, A, 1000

2012, B, 2000

2013, X ,3000

];

Bridgetable:

LOAD Key,

Year,

Product

Resident of Table1;

LOAD Key,

Year,

Product

Resident of Table2;

Drop fields Year,Product from Table1;

Drop fields Year,Product from Table2;

Re: Composite Key

nihhalmca — Fri, 12 Jun 2015 07:54:45 GMT

Thanks Peter.