Joining more tables by different keys

[#1]

Ciao!

Obviously I have a problem!

I have 3 tables.

Table1 with fields:

key1

key1&key2

field1

field2

…

field n

Table2 with fields:

key1&key2

price1

Table 3 with fields:

key1

price2

My question is how to assosciate to key1&key2 of Table1 to the field price1 in Table2 if key1&key2 exists also in Table2, while associate the field price2 in Table3 (by the field key1) if key1&key2 does not exist in Table2, without generating loops between tables.

How can I solve the question?

Thanks in advance!

[pat_agen · Accepted solution]

  hi Elena,

one way to go would be by adding the field Price (either 1 or 2) to table1 and then drop Tables 2 and 3 if these tables have no other purpose than to provide the Price.

Does this work for you?

If so here is a way to do it

// load tables 2 and 3 as mapping tables

mapPrice1:
mapping load key1&key2,
  price1
from datasourceTable2;

mapPrice2:
mapping load key1,
  price2
from datasourceTable2;

Table1:
load
*,
applymap('mapPrice1',key1&key2,applymap('mapPrice2',key1)) as price
;

Table1:
load *
from datasourceTable1;

              
hope this helps

[#3]

Thanks a lot pat!

Just a question: if in Table2 and Table3 I have a third field let's say "Price list" what happens? I cannot apply maps in this case...

[pat_agen]

hi Elena,

when you do a mapping load the data from the original load is not stored in the qvw document.

The "mapping" keyword before the load causes a temporary table - which exists for the duration of the script run - to be created. these tables always have just the two columns.

If you need information from the tables 2 and 3 - such as your price list - you can load them as ordinary tables. You can do this prior to creating the mapping table (preferable) as you have only to read your external Data source once.

You would then create your mapping table with code as follows

load x, y

resident table2;

You probably can find a solution whereby you find your price dynamically - as opposed to my solution which has added the correct price to table1. The advantage of doing it in the script is to make all your expressions much simpler, which is helpful for performance and I'd argue future maintenance.

hope this is clear. If not don't hesitate.