0 Replies Latest reply: Sep 8, 2011 10:16 AM by Donald Posthuma RSS

    advanced joining/grouping

    Donald Posthuma

      Hi everybody,

       

      Again a question about joining and grouping. I have the following case. There are 2 tables which contain data. Both are filled from several companies so I use a loop to fill them. These to tables need to be joined but first a group by needs to be executed on the first table. After the join the table needs another group by. So in script I have the following.

       

       

      First Load both tables and group the first.

       

      Files [X Y]

      for i = 1:n

      Let vFile Files(i)

       

       

      TableA

      LOAD

            Field1

            Field2

      GroupBy

           Field 1

      LOAD

           Field1

           Field2

      From DatabaseA$(vFile)

       

      TableB

      LOAD

           Field1

           Field3

      From DatabaseA$(vFile)

       

      NEXT

       

      Join both tables based on Field1

       

      LEFT JOIN (TableA) LOAD

           *

      RESIDENT

           TableB

       

       

       

      Group the joined table based on Field2

       

      LOAD

           Field2

           Field3

      RESIDENT

           TableA

      GROUP BY Field2

       

       

       

      I would rather make my script a little bit more compact and I'm sure this is possible but somehow the joining within the loop gives all types of problems. Any suggestions?