Nested LOAD

[#1]

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 !!

[pat_agen · Accepted solution]

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))

[Anonymous]

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.

[ToniKautto]

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;

[#5]

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

[#6]

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 !!!