Left joins with resident table

[tmumaw]

Does anyone what could be wrong with my left join statement? I know I am missing something real simple.

Thanks

ZTRPSTEMP:
LOAD %ContractItemKey as ContractItemKey,
Status,
Date,
date(floor(weekend(Date, 0, -1)),'YYYY-MM-DD') as WEDATTemp,
sum(Hours) as [Hrs Worked]
FROM (qvd)
where Status = 'C' //and %ContractItemKey = '00425409810020'
group by %ContractItemKey, Status, Date;

Join (ZTRPSTEMP)
LOAD %ContractItemKey,
Status,
Date,
date(floor(weekend(Date, 0, -1)),'YYYY-MM-DD') as WEDATTemp,
sum(Hours) as [Hrs Worked]
FROM (qvd)
// where %ContractItemKey = '00425409810020'
group by %ContractItemKey, Status, Date;

ZTRSPSTEMP1:
noconcatenate
load
%ContractItemKey,
Status,
Date,
date(floor(weekend(Date, 0, -1)),'YYYY-MM-DD') as WEDATTemp,
[Hrs Worked],
if(ContractItemKey = %ContractItemKey,'1',2) as [Complete Ind]
resident ZTRPSTEMP;

ZTRSPSTEMP1:
LEFT JOIN (VBAKTemp)
LOAD
%ContractItemKey,
Status,
Date,
date(floor(weekend(Date, 0, -1)),'YYYY-MM-DD') as WEDATTemp,
[Hrs Worked]
RESIDENT ZTRPSTEMP1;

[disqr_rm]

I understand in first load from ZTRPST.QVD you are getting sum of hours worked against completed (Status = 'C') contracts. Not sure what you are doing in second time load. What are you trying to join?

[johnw]

Well, there's no table VBAKTemp table defined to join to. You might have one in the script, but it isn't in the section of the script you posted. You also shouldn't state your source table name ABOVE the left join. That's what your RESIDENT statement is for.

[tmumaw]

Ok. This is what I am trying to do. I have a table called ZTRSPST. This table contains jobs we have worked and completed, and jobs we have worked and not completed. Here is an example:

Contract / Line(Key) Date Status Hours Worked

0042540981 / 0020 02/07/2010 I 12

0042540981 / 0020 02/07/2010 I 6

0042540981 / 0020 02/10/2010 C 12

0042540981 / 0020 02/10/2010 C 6

If a job has not yet been completed (no "C"), then I need to use the incomplete ("I"), else if the job has been completed I need to use "C". Is there an easy way to do this?