Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Nested LOAD

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

1 Solution

Accepted Solutions
pat_agen
Specialist
Specialist

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

View solution in original post

5 Replies
pat_agen
Specialist
Specialist

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
Not applicable
Author

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

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;


Not applicable
Author

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

Not applicable
Author

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