topic Join 2 tables but cannot calculate with the fields in QlikView

Join 2 tables but cannot calculate with the fields

Mon, 27 Apr 2009 15:55:18 GMT

elle Fellow users,

I have joined the tables JC with JC.Accruals and I would like to calculate with those fields. I would like to add the fild JOB_NUMBER (TABLE JC) With the field labor (table JC.ACCRUALS). When I run the script the system gifs me the following massage field not found-<JC.Accruals.Labor>.

Could someone help me with this. Below you will find the script.

thanks,

Aissam

JC:

JOB_NUMBER,

LABOR_ACTUAL,

LABOR_ACTUAL + JC.Accruals.Labor as LaborActual

from table;

-----------------------------------------------------------------------------

Qualify*;

UnQualify

JOB_NUMBER;

JC.Accruals:

left

join (JC) LOAD JOB_NUMBER,

Labor

FROM [Data Dump\JobCost\Late charge 2009.xls] (biff, embedded labels, table is [Accruals$]);

Unqualify

Join 2 tables but cannot calculate with the fields

sparur — Mon, 27 Apr 2009 18:24:40 GMT

Hi, Aissam

Try this way:

tmpJC:
LOAD
JOB_NUMBER,
LABOR_ACTUAL
from table;

left join (tmpJC)
LOAD JOB_NUMBER,
Labor
FROM [Data Dump\JobCost\Late charge 2009.xls] (biff, embedded labels, table is [Accruals$]);

JC:
NOCONCATENATE
LOAD *,
LABOR_ACTUAL + Labor as LaborActual
RESIDENT tmpJC;

DROP TABLE tmpJC;

Join 2 tables but cannot calculate with the fields

disqr_rm — Mon, 27 Apr 2009 18:53:23 GMT

Hi,

If you are doing left join on tmpJC with a key figure (Labor) field I would recomment doing a sum for Labor. Otherwise, in case if you have more than one record in your xls file somehow, it will mess up the results. So something like this:

left join (tmpJC)
LOAD JOB_NUMBER,
sum(Labor) as Labor
FROM [Data Dump\JobCost\Late charge 2009.xls] (biff, embedded labels, table is [Accruals$])
group by JOB_NUMBER;

Hope this helps.

Join 2 tables but cannot calculate with the fields

Tue, 28 Apr 2009 01:12:28 GMT

Thanks for your comment, just one question. Why do you use noconcatenate?

Regards,

Aissam