Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Joining more tables by different keys

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!

1 Solution

Accepted Solutions
pat_agen
Valued Contributor

Joining more tables by different keys

  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 Replies
pat_agen
Valued Contributor

Joining more tables by different keys

  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

Not applicable

Joining more tables by different keys

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
Valued Contributor

Joining more tables by different keys

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.

Community Browser