topic Re: Composite Key (unwanted multiplication) in QlikView

Composite Key (unwanted multiplication)

Thu, 08 Jan 2015 10:32:48 GMT

Hi guys,

I'm creating a composite key out of my tables Sales and Product in my QV Application:

Temp:

LOAD Id as SalesId

FROM

Sales.qvd

LEFT JOIN (Temp)

LOAD Id as ProductId,

SalesId

FROM

Product.qvd

CompositeTable:

LOAD

SalesId&'|'&ProductId as Comp_Key

RESIDENT Temp;

DROP TABLE Temp;

The output for the Comp_Key is the following:

SalesId1 | ProductId1

SalesId1 | ProductId2

SalesId1 | ProductId3

SalesId2 | ProductId1

SalesId2 | ProductId2

SalesId2 | ProductId3

How can I prevent the generation of multiple SalesId1 with diffrent ProductId's? I only need one composite key for each SalesId.

Many thanks,

Harry

Re: Composite Key (unwanted multiplication)

alexandros17 — Thu, 08 Jan 2015 10:38:04 GMT

It is not possible with your example:

Prod

with the left join you have:

1 P1

1 P2

2 P1

2 P2

3 P1

3 P2

(obviously in the case that each ID sells all the products)

So now when generating your composite key which product should you choose?

The one you have is a natural association coming from the left join.

Explain better what you want to do

Re: Composite Key (unwanted multiplication)

datanibbler — Thu, 08 Jan 2015 10:38:27 GMT

Hi HarryLee,

sorry, I don't quite get you - the point of creating a composite key is having a key that will uniquely identify every single line in your table, no?

=> If you still have duplicates with that composite key, something is wrong, the composite key is not doing what it should, so you probably have to change or enhance it by using another field.

HTH

Best regards,

DataNibbler

Re: Composite Key (unwanted multiplication)

Thu, 08 Jan 2015 11:53:00 GMT

Thanks! In regards to my example I want the following:

Each Sale can have multiple products. What I want is to join both tables with an unique Id. Furthermore I only want the products associate with the corresponding sale. However a product can be used in different Sales.

My table should look like the following in the end:

Comp_Key SalesId ProductId SalesName ProductName

xy x y FirstSale Banana

xz x z FirstSale Apple

ay a y SecondSale Banana

az a z SecondSale Apple

Hope that helps to understand my goal. Can someone give me an easy example how to achieve this?

Many thanks

Harry

Re: Composite Key (unwanted multiplication)

Thu, 08 Jan 2015 13:45:33 GMT

Any ideas?

Best

Harry

Re: Composite Key (unwanted multiplication)

rubenmarin — Thu, 08 Jan 2015 14:17:44 GMT

Hi, you can create the composite key when you are loading the products table:

LEFT JOIN (Temp)

LOAD Id as ProductId,

SalesId,

SalesId & '|' & Id as Comp_Key

FROM

Product.qvd

As for the data you get, If SalesId1 has sales of ProductId1, ProductId2 and ProductId3, it will create the 3 different composite keys:

SalesId1|ProductId1

SalesId1|ProductId2

SalesId1|ProductId3

If you want a field that identifies a sale it is the "SalesId" Field

Re: Composite Key (unwanted multiplication)

Fri, 09 Jan 2015 10:51:44 GMT

Many thanks to all of you! I got a solution

Best

Harry