topic Error: Field not found in QlikView

Error: Field not found

Tue, 08 Dec 2009 01:55:01 GMT

Hi there, I can't understand - what's wrong with this code:



StatDataTmp:
LOAD Region,
 Plant,
 Duration,
 Sum as PlanCost
FROM
[Some.xlsx]
(ooxml, embedded labels, table is [SQL Results]);

StatDataByPlant:
load Plant as Plant,
 sum(PlanCost) as SumPlanCost
resident StatDataTmp
group by Plant;

left join (StatDataByPlant)
load Region,
 Plant,
 Duration,
 PlanCost,
 (PlanCost/SumPlanCost)*100
resident StatDataTmp;

drop table StatDataTmp;

Exception is "Field not found - <SumPlanCost> ..."

Error: Field not found

Oleg_Troyansky — Tue, 08 Dec 2009 02:26:36 GMT

You view your "join" load as if it's an SQL join, - expecting fields from both tables to be available... QlikView join works a bit different... You can only use fields from your "source" table, but not from the table that you join to. To make this code work, you need to join the SumPlanCost into the detailed table prior to the final calculation.Join the total, then reload the table and calculate the formula.

good luck!

Error: Field not found

rwunderlich — Tue, 08 Dec 2009 02:28:54 GMT

The LOAD statement can only reference fields in a single table - in this case StatDataTmp.

Another approcah would be to do the left join to get all the data into one table and then use another load to create the new field.

RIGHT JOIN (StatDataByPlant) LOAD DISTINCT * (PlanCost/SumPlanCost)*100 AS CostRatio
RESIDENT StatDataByPlant;

-Rob

Error: Field not found

Tue, 08 Dec 2009 22:20:31 GMT

unfortunately this code:


RIGHT JOIN (StatDataByPlant) LOAD DISTINCT * (PlanCost/SumPlanCost)*100 AS CostRatio
RESIDENT StatDataByPlant;

doesn't work - the same error.

But if I remove expression with field "SumPlanCost" - all coorrect ...

Working version of code:



StatDataTmp:
LOAD Region,
 Plant,
 Duration,
 Sum AS PlanCost
FROM
[Some.xlsx]
(ooxml, embedded labels, table is [SQL Results]);

StatDataByPlant:
LOAD Plant,
 sum(PlanCost) AS SumPlanCost
RESIDENT StatDataTmp
GROUP BY Plant;

JOIN(StatDataByPlant)
LOAD *
RESIDENT StatDataTmp;

Error: Field not found

Tue, 08 Dec 2009 22:26:41 GMT

Thanks, Oleg. Really, my understanding of the joins in QV was a little bit wrong 🙂 - legasy of the work with RDBMS.

Error: Field not found

Oleg_Troyansky — Tue, 08 Dec 2009 23:54:24 GMT

No sweat - it's a very common "mental switch" that every new QV developer goes through, after getting used to the standard SQL logic 🙂

welcome to the club!

Error: Field not found

rwunderlich — Wed, 09 Dec 2009 02:08:30 GMT


Dmitry wrote:RIGHT JOIN (StatDataByPlant) LOAD DISTINCT * (PlanCost/SumPlanCost)*100 AS CostRatio
RESIDENT StatDataByPlant

As I said in my post (not too clearly I guess), this code gets added after the JOIN of the two tables. That is, only if you need the calculated field.

-Rob

Error: Field not found

Wed, 09 Dec 2009 18:39:28 GMT

Great thanks, Rob - now it's clear for me.

Regards, Dmitry.