Question about left join and where

[#1]

Hi

I've searched around a bit on the forums but haven't found anything really relevant, although I might have missed it 🙂

Anyway, I have 2 tables which i load from QVD files. Both tables have a field, lets call it Field1 which has many identical values in both tables. The first table also has a field, we can call it Field2.

What I want to do is to join these to tables and ONLY load rows from Table2 where Field2 in Table1 is greater than a specific value.

I've tried to do it like this:

Load
Field1,Field2
FROM Table1.qvd;

Left join Load
Field1, Field3
FROM Table2.qvd where Field2>10;

But this doesnt work (of course). How do I accomplish this?

[Anonymous · Accepted solution]

One way to accomplish this is to join both tables as is, and after that run another load to eliminate records:

table_tmp:
Load
Field1,Field2
FROM Table1.qvd;
Left join (table_tmp) Load
Field1, Field3
FROM Table2.qvd;
NOCONCATENATE
table:
LOAD
Field1,Field2,
if(Field2>10, Field3) as Field3
RESIDENT table_tmp;

Another way, which probably will by my preference, is to use mapping:

table:
Load
Field1,Field2
FROM Table1.qvd;
Map:
LOAD DISTINCT
Field1 as A,
Field2 as B
RESIDENT table;
LEFT JOIN (table) LOAD
Field1,
FROM Table2.qvd
where applymap('Map',Field1,0)>10;