Joining two tables with similar structure by link table

[Anonymous]

Colleagues, good day!

Try to describe you my task:

In my real model i have secondary sales of distributors (Distributor_Id) (sales to real shops). Shops - is DeliveryPoint_Id.

I need to join for current structure primary sales - sales to distributors.

The main thing -to save current structure: Table with Secondary sales, LinkTable and Products.

LinkTable necessarily must have current fields:

Secondary_Sales_Key

Nomenclature_Id

DeliveryPoint_Id

Warehouse_Id

Distributor_Id

Дата

Main link between secondary and primary sales is Warehouse_Id - DeliveryPoint_Id_Primary. 

Also primary table has same field Nomenclature_Id and Дата. It's the main promblem to connect correctly it to Secondary sales by LinkTable. I think it must be some auto combine Key link table (WareHouse_Id, Nomenclature_Id, Дата) or some other.

In real model Warehouse_Id can have two or several same DeliveryPoint_Id_Primary, and reverse situation: DeliveryPoint_Id_Primary can have several same WareHouse_Id, thats why primary sales must not be dublicated.

In attachment qvd's and test model.

Also i've attached in excel file test result table with secondary and primary sales.

Many thanks for your advices and help.

[marcus_sommer]

If I understand it right you have a main- respectively first-sale and afterwards further sales which you want to differentiate, correct?

Then you need any markers or logic to identify them. In your test-app both tables (secondary and link) contain the same records (yes there are other fields and they are connected per key but it are the same records) - this approach will work if you had such identifier and you would filter the tables appropriate.

But I didn't understand your data well enough to say if you could identify with your used key-fields both types of sales. I assume rather not - normally you would need parent-child fields between your records - this meant the ERP system must create them.

If such a direct logic is missing is often a check of records against previous records in a sorted loading the only possibility to create those dependencies in extra-fields - here is an excellent description how it could be done: Peek() or Previous() ?

- Marcus

[Gysbert_Wassenaar]

I'm afraid you are going to have to ask the owner of the source system how primary and secondary sales are related. In fact, ask first if they are actually directly related. We can't answer these questions for you.

[vikasmahajan]

PFA

Vikas

[Anonymous]

Good day!

Secondary and primary sales are related directly by following link:

Warehouse_Id - DeliveryPoint_Id_Primary

i don't know how to connect them, because they has the same fields (Nomenclature_Id, Дата) and i don't know how to make correct key (to avoid synth key) and link this two tables by link table.

Will be very glad for your help!

Happy New Year!

Thanks.

[Anonymous]

Good day!

In your solution result table has not entirely correct link Warehouse_id-Delivery_Point_Id_Primary.

And of course, i need to connect this tables by link table (with joining, not concatenation)

Thanks.

[Anonymous]

Necessary structure in attachment