Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

andreyfcdk91
Contributor

Joining two tables with similar structure by link table

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.

6 Replies
MVP & Luminary
MVP & Luminary

Re: Joining two tables with similar structure by link table

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

MVP & Luminary
MVP & Luminary

Re: Joining two tables with similar structure by link table

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.


talk is cheap, supply exceeds demand
vikasmahajan
Esteemed Contributor

Re: Joining two tables with similar structure by link table

PFA

Vikas

andreyfcdk91
Contributor

Re: Joining two tables with similar structure by link table

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.

andreyfcdk91
Contributor

Re: Joining two tables with similar structure by link table

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.

andreyfcdk91
Contributor

Re: Joining two tables with similar structure by link table

Necessary structure in attachment