3 Replies Latest reply: Jun 8, 2011 9:38 AM by Patrick Laredo RSS

    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!

        • Joining more tables by different keys
          Patrick Laredo

            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

            • 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...

                • Joining more tables by different keys
                  Patrick Laredo

                  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.