2 Replies Latest reply: May 1, 2009 8:38 PM by Oleg Troyansky RSS

    LOAD from a series of LEFT Joined tables

      Hi,

      I have 4 LOAD statements all LEFT joined together to form one 'entity'

      I want to be able to perform a LOAD on this 'entity' using some IF statements to manipulate some data.

      However, if I perform another LOAD immediately before the first table in the JOINs it complains that it cannot see a field which exits in the 2nd table in the Join.

      How can I acheive this..

      for example, this is what I am trying to do.

      LOAD
      IF(table2.field1 = 'ACR', 'Hello', table1.Category) as MainCategory
      ;

      LOAD Table 1;

      LEFT JOIN LOAD Table 2;

      LEFT JOIN LOAD table 3;

      LEFT JOIN LOAD table 4;

      I hope this explains things slightly clearer!

      thanks in advance,

      Matt

       

       

       

       

        • LOAD from a series of LEFT Joined tables

          The only way the one table can see a field in a nother table is if you join the tables together then load the data in to a resident table.
          Example:

          Table:

          Load

          Field1,

          Field2,

          FROM test.qvd;

          Left join

          Load

          Field 3 ,

          Field 4

          FROM test1.qvd

           

          Load

          Field1

          Field2

          Field3

          Field4

          RESIDENT Table;


          Drop table table;

          • LOAD from a series of LEFT Joined tables
            Oleg Troyansky

            Matt,

            after your 4 joins, the overall resulting table will be Table 1. You'll need to drop tables 2, 3 and 4.

            As far as your if() statement, you can do one of the two:

            1. Reload the final resulting table after all the joins are made - this is the most readable solutions, and I'd prefer it:

             


            FinalTable:
            load
            *,
            IF(table2.field1 = 'ACR', 'Hello', table1.Category) as MainCategory
            resident Table1
            ;
            drop table Table1;
            <pre>

            Another way - if you REALLY want to use preceding load, you should position it right before the last (4th) join.
            regards,
            Oleg</body>