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