LOAD from a series of LEFT Joined tables

[#1]

Hi,

I have 4 LOAD statements all LEFT joined together to form one 'entity'

I want to be able to perform a LOAD on this 'entity' using some IF statements to manipulate some data.

However, if I perform another LOAD immediately before the first table in the JOINs it complains that it cannot see a field which exits in the 2nd table in the Join.

How can I acheive this..

for example, this is what I am trying to do.

LOAD
IF(table2.field1 = 'ACR', 'Hello', table1.Category) as MainCategory
;

LOAD Table 1;

LEFT JOIN LOAD Table 2;

LEFT JOIN LOAD table 3;

LEFT JOIN LOAD table 4;

I hope this explains things slightly clearer!

thanks in advance,

Matt

[#2]

The only way the one table can see a field in a nother table is if you join the tables together then load the data in to a resident table.
Example:

Table:

Load

Field1,

Field2,

FROM test.qvd;

Left join

Load

Field 3 ,

Field 4

FROM test1.qvd

Load

Field1

Field2

Field3

Field4

RESIDENT Table;

Drop table table;

[Oleg_Troyansky]

Matt,

after your 4 joins, the overall resulting table will be Table 1. You'll need to drop tables 2, 3 and 4.

As far as your if() statement, you can do one of the two:

1. Reload the final resulting table after all the joins are made - this is the most readable solutions, and I'd prefer it:

FinalTable:
load
 *,
 IF(table2.field1 = 'ACR', 'Hello', table1.Category) as MainCategory
resident Table1
;
drop table Table1;
<pre>

Another way - if you REALLY want to use preceding load, you should position it right before the last (4th) join.
regards,
Oleg</body>