Error: Field not found

[#1]

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> ..."

[Oleg_Troyansky]

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!

[rwunderlich]

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

[#4]

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;

[#5]

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

[Oleg_Troyansky]

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!

[rwunderlich]

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

[#8]

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

Regards, Dmitry.