topic Left joins with resident table in QlikView

Left joins with resident table

tmumaw — Mon, 22 Feb 2010 21:21:50 GMT

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;

Left joins with resident table

disqr_rm — Mon, 22 Feb 2010 21:54:18 GMT

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?

Left joins with resident table

johnw — Mon, 22 Feb 2010 21:54:44 GMT

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.

Left joins with resident table

tmumaw — Tue, 23 Feb 2010 12:44:47 GMT

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?