Composite Key (unwanted multiplication)

[#1]

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

[alexandros17]

It is not possible with your example:

ID

1

2

3

Prod

P1

P2

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

[datanibbler]

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

[#4]

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

[#5]

Any ideas?

Best

Harry

[rubenmarin]

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

[#7]

Many thanks to all of you! I got a solution

Best

Harry