Multiple associations between 2 tables

[paul_malfait]

Hi,

I have a database of sales contracts with 2 tables (I'm simplifying):

1. Contracts, with fields
   
   1. Seller ID
   2. Buyer ID
   3. + lots of other info
2. Parties, with fields
   1. Party ID
   2. Country
   3. + lots of other info

So the detailed info of all the parties of the contracts is in the unique Parties table, and the Contracts table only contains the links from Seller ID to Party ID and from Buyer ID also to Party ID.

Now, with Qlik Sense, I try to generate charts that use e.g. the country of the seller and/or the country of the buyer.
Like a pie chart with the contracts per seller country and one with the contracts per buyer country.
Or a table of the contracts with columns for the seller country and the buyer country.

But I don't find how I can associate the tables to make this possible (if it is possible at all).

Looking forward to any help.

[ArnadoSandoval]

Hi @paul_malfait 

This is a bit of air-code, but I hope it illustrates a potential solution; you create two instances of the Parties table, I called them Parties_Seller and Parties_Buyer;  commenting out the conflicting field in these instance, and with the Contracts table, I aliased the columns involved.

Contracts:
Seller_ID    As %Key_to_seller,
Buyer_ID     As %Key_to_buyer,
+ lots of other info;

Parties_Seller:
Party_ID,
Seller_ID  As %Key_to_seller,
// Buyer_ID,
Country
+ lots of other info;

Parties_Buyer:
Party_ID,
// Seller_ID,
Buyer_ID   As %Key_to_buyer
Country
+ lots of other info;

 Hope this helps,

[ArnadoSandoval]

@paul_malfait 

Even though we created two instances of the Parties table, Qlik does not duplicate its memory requirements for this data.

hth

[Vegar]

A comment/adjustment to @ArnadoSandoval  suggested solution. 

Try to make all the field names but the key field names unique in the buyer and seller tables. If not you will get both Syntetic keys and circular references in your dates model. 

Something like this :

Contracts:

Seller_ID As %Key_to_seller,

Buyer_ID As %Key_to_buyer,

+ lots of other info;

Parties_Seller:

Party_ID as [Seller Party_ID] ,

Seller_ID As %Key_to_seller,

Country as [seller country], 

[other info] as [seller other info] ;

Parties_Buyer:

Party_ID as [Buyer Party_ID] ,

Buyer_ID As %Key_to_buyer

Country as [buyer country], 

[other info] as [buyer other info] ;

[paul_malfait]

Thank you Arnado for your prompt answer.

Could you give me a hint on how to create two instances of the same table, without loading twice?

My data sources are Excel files

[ArnadoSandoval]

Sure @paul_malfait , I will use @Vegar  code to illustrate the load script code!

Parties_Seller:
Load
     Party_ID     As [Seller Party_ID] ,
     Seller_ID    As %Key_to_seller,
     Country      As [seller country], 
     [other info] As [seller other info]
From <parties table at data base or data source>;

NoConcatenate

Parties_Buyer:
Load 
     Party_ID     As [Buyer Party_ID] ,
     Buyer_ID     As %Key_to_buyer,
     Country      As [buyer country], 
     [other info] As [buyer other info] ;
From <parties table at data base or data source>;

Very important, as you are loading the parties data into two different tables (Seller and Buyer instances), you should make sure that each column has an alias prefixing its corresponding instance, otherwise as @Vegar commented you will get way too many/complex synthetic keys,  if you look at the Country column by example, you will get a column as [seller country], and another as [buyer country].

You may also find that some columns from the parties table concern uniquely to Buyers or Sellers, for those columns you can keep them in the corresponding instance and exclude from the others.

There are other ways to implement this structure, but I believe this explanation will be enough for now for you.

Hope this helps,

[paul_malfait]

Very helpful.

One thing I don't get: In the parties table I only have one Party ID field.
I understand that in the first instance I load it as [Seller Party_ID], and in the second as [Buyer Party_ID], and then I can link  [Seller ID] from the Contracts table to [Seller Party_ID] in Parties_Seller, and [BuyerID] from the Contracts table to [Buyer Party_ID] in Parties_Buyer.

But what is the second line in both LOAD statements doing? There is no Seller_ID or Buyer_ID in the parties table. Only the Party_ID that was loaded in the first line.

[ArnadoSandoval]

Hi @paul_malfait 

Well, You initial post did not do a good job explaining your issue! Now if you kindly post the real name of the tables involved and their fields, because we had been writing "air code", and "air code" illustrates ideas for you to follow.