Help! Preceding Load Script Error: Field not found

[#1]

Hi Guys,

One too many headaches trying to get this to work and hoping the folks here can help. In words Im trying to load certain fields from a resident table which I will give a new alias, as I want the straight table to work independently of the document. It will only be linked by ClientName

I have a preceding load which I use as a having a clause (not sure even if this is done right). Whenever I load I get an error "Field not found". It does not tell which fields but does list the relevent LOAD. It is the second load which includes the recno(). I'm sure there are many mistakes, so please correct any others you see haha, thanks!

Duplicate:
LOAD
ClientName,
TempName as d_tempname,
CategoryDesc as d_categorydesc,
WkEndDate as d_wkenddate,
TimesheetNo as d_timesheetno,
Invoice as d_invoice,
Duplicate as d_duplicate,
Hours,
ChargeValue
Where d_duplicate='Check';
LOAD
RowNo() as d_rownoID,
ClientName,
TempName as d_tempname,
CategoryDesc as d_categorydesc,
WkEndDate as d_wkenddate,
TimesheetNo as d_timesheetno,
Invoice as d_invoice,
Duplicate as d_duplicate,
Fabs(Hours) as d_positivehours,
if(Hours<0,'N','P') as d_signhours,
Fabs(ChargeValue) as d_positivechargevalue,
ChargeValue as d_chargevalue
Resident SPInvoicingPreFinal
Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours;

[Or]

Looks like you almost had it - the problem is with the very last line. As with SQL queries, in Load statements, you must use the original field name rather than the alias.

Order by TempName ,  WkEndDate  ,  CategoryDesc  , Fabs(Hours)

[#3]

Hi Byron_van_wy,

It seems you are trying to concatenate the data from your second load statement to the duplicate table.

In that case what you would like to do is use CONCATENATE before the second reload statement. Which will help to not create sysnthetic keys in the buffer memory.

Again, a doubt, correct me if I am wrong.

Why are you loading the same fields in both of your load statement. It seems many of the field are loaded twice with two different load statements.

For a preceeding load I try this way,

// Preceeding Load statement.

Load *,

if(Month<Month(Today()), 'Yes','No') as Month_Flag

;

Temp:

Load

Date,

Month(Calendar) as Month,

Year(Calendar) as Year,

and so on

;

Hope this helps,

Bikash

[#4 · Accepted solution]

Thanks for the reply guys. Both posts gave me ideas as to how to fix my problem. Below is the solution that worked for me

DuplicatePrefinal:
LOAD *
Where d_duplicate = 'Check';
LOAD
ClientName,
TempName as d_tempname,
CategoryDesc as d_categorydesc,
WkEndDate as d_wkenddate,
TimesheetNo as d_timesheetno,
Invoice as d_invoice,
Duplicate as d_duplicate,
Hours as d_hours,
if(Hours<0,'N','P') as d_signhours,
Fabs(Hours) as d_positivehours,
Fabs(ChargeValue) as d_positivechargevalue,
ChargeValue as d_chargevalue
Resident SPInvoicingPreFinal
Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;


DuplicateFinal:
LOAD *,
rowno() as d_rownoID,
1 as Dummy //Dummy field to not force automatic concatenation
Resident DuplicatePrefinal
Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_timesheetno, d_invoice;
Drop Table DuplicatePrefinal;
Drop Field Dummy;