5 Replies Latest reply: Apr 29, 2011 4:58 PM by Josué Andrade RSS

    Nested LOAD

    Josué Andrade

      Hi,

       

      It is possible to use a Nested LOAD, like we do with SQL ??

      Here's a example:

      Table1:

      LOAD field1,

      field2,

      periodo

      FROM table1.qvd (qvd)

      WHERE Month(Periodo) = (LOAD RangeMax(Month(Periodo)) FROM Table2.qvd (qvd))

       

      I've tried that, and didn't work.

      Any ideas how to solve that ? Or there's a way to save the result of (LOAD RangeMax(Month(Periodo)) FROM Table2.qvd (qvd))

      in a variable, and use it in the Where clause ?

       

      Thanks !!

       

       

        • Nested LOAD
          Pat.Agen

          hi,

          try this code

          Table2:
          load
          Periodo
          from table2.qvd (qvd);

          findMaxMonth:
          load Max(Month(Periodo)) as MaxMonth
          resident Table2;

          let varMaxMonth=peek('MaxMonth',-1,findMaxMonth);

          drop table findMaxMonth;

          Table1:
          LOAD field1,
          field2,
          periodo
          FROM table1.qvd (qvd)
          WHERE num(Month(Periodo)) = $(varMaxMonth) FROM Table2.qvd (qvd))

          • Nested LOAD
            Jeffrey Vermeire

            Hi Josué,

            I think that there are several ways to accomplish this, but, in my opinion, the simplest is to load your max month into a variable.

             

            Like so:

             


            tmp:
            LOAD RangeMax(Month(Periodo)) AS maxmonth FROM Table2.qvd (qvd);

            LET vMaxMonth = FIELDVALUE('maxmonth',1);

            DROP TABLE tmp;

            Table1:
            LOAD
            field1,
            field2,
            periodo
            FROM
            table1.qvd (qvd)
            WHERE (
            MONTH(periodo) = $(vMaxMonth)
            );


             

            I haven't tested that, but I've used similar load statements. Hopefully this helps.

              • SV:Re: Nested LOAD
                Toni Kautto

                I think a joined load would be a bit cleaner, since you dont have to create variables or drop tables.

                 


                // Randomized test data

                Data:
                Load
                Ceil(Rand()*10) as ID,
                RecNo() as Value
                autogenerate 20;

                Result:

                //Load max value by sorting in descending order, and only include first record
                LOAD
                ID as ResultID
                Resident Data
                Where RecNo() = 1
                Order By ID desc;

                //Load the data table with an inner join to only include the max value related rows
                Inner Join
                LOAD
                ID as ResultID,
                Value as ResultValue
                Resident Data;


              • Nested LOAD
                ivan_cruz

                Hi Josué, in my experience joining and where statement can be slower than keep syntaxis when working with millions of records, so you may try this:

                Table1:

                LOAD field1,

                field2,

                periodo

                FROM table1.qvd (qvd)

                Table2:
                right keep(Table1)

                load
                max(Periodo) as Periodo
                from table2.qvd (qvd);

                 

                drop table Table2;

                 

                Regards

                  • Nested LOAD
                    Josué Andrade

                    Because I have to use this filter in many other tables, I used the variable solution, and worked fine.

                    The Join solution I'll try later.

                    Thank you all guys, this forum is saving me a lot of time !!!