Simplify code in load statements

[#1]

I have some very long formulas within a Load Statement and some of the fields depend on the value of the field above.

Rather than nest the code for the line above in the new line which gets difficult to read is there a way to define a variable within the load or another way to simplify it?

[johnw · Accepted solution]

You can use preceeding loads to load from the table so far and add fields based on other fields. For a simple example:

[Something]:
LOAD *
,if("Category"='A' and "Month"=date(20090501,'YYYYMMDD'),"Amount") as "May 09 Category A Amount"
;
LOAD *
,date(monthstart("Date")) as "Month"
;
LOAD
date(date#(20081231,'YYYYMMDD')+recno()) as "Date"
,mid('ABCDE',ceil(rand()*5),1) as "Category"
,ceil(rand()*1000) as "Amount"
AUTOGENERATE today()-date#(20081231,'YYYYMMDD')
;

[#3]

Thank you - very helpful