Preceding Load and Join

[#1]

This is a very simplified example of my table. I don't think the preceding load can find the fields since they are not in the load, but in the two left joins.

Appreciate all of the help!!

Thanks!

Table1

Load *,

JoinedA +JoinedB= Field1,

JoinedC+JoinedD=Field2;

Load

id

name

phone

From

left join(Table1)

id

status as JoinedA

address as JoinedB

Resident Othertable

group by id

left join(Table1)

id

gender as JoinedC

age as JoinedD

Resident Othertable2

group by id

[agni_gold]

Please elaborate your question

[Gysbert_Wassenaar]

That's right. You first have to join the tables so all the fields exist in one table. Then you can use them for further calculations.

But it your case you can add up the fields in the other loads:

Table1

Load

     id,

     name,

     phone

From ...;

left join(Table1)

Load

     id,

     status as JoinedA,

     address as JoinedB,

     status + address as Field1

Resident Othertable

;

left join(Table1)

Load    

     id,

     gender as JoinedC,

     age as JoinedD,

     gender + age as Field2

Resident Othertable2

;

Though I don't see how you'd get any sane result out of a sum of status and address. Perhaps you want to do string concatenation instead: status & ' - ' & address

[#4]

I just used example field names- I am not adding Status and Address.

Once I do the 2 left joins, how do I put Table1 and the 2 left joins to another table? so that I can put the preceding load and the apply maps there?

[Gysbert_Wassenaar]

After the two left joins you have only one table left: Table1. You can use that table for a resident load to create a new table.

NewTable:

LOAD

     *,

     'MyValue' as NewField

RESIDENT

     Table1

     ;