Field cannot be found when trying to load the script

[KayPong]

When I am trying to load Line Y in the following script in the Load Script Editor, I am returned with an error that [KPI.Target] cannot be found  whereas Line X is perfectly ok without the presence of Line Y. May I have your advice on what I had done incorrectly?

[Overview]:

Load
[KPI Key]
, [KPI.Target] as [Target] //Line X
, [KPI.Threshold] as [Threshold]

Resident [KPI];
drop Tables [KPI];

Left Join ([Overview])

Load
[KPI Key]
,sum([A.Case - Dengue])/$(vPopYear)*100000 as [Measurement]
,if( sum([A.Case - Dengue])/$(vPopYear)*100000 < [KPI.Target] , 1, 0) as [Green]  // Line Y

resident [A]
group by [KPI Key];

[KayPong]

Can someone provide me some light to the above? thank you!

[brunobertels]

Hi

you rename KPI.Target as Target , but call in your left join KPI.target 

try to change your mesure :

,if( sum([A.Case - Dengue])/$(vPopYear)*100000 < [Target] , 1, 0) as [Green]  // Line Y

[Overview]:

Load
[KPI Key]
, [KPI.Target] as [Target] //Line X
, [KPI.Threshold] as [Threshold]

Resident [KPI];
drop Tables [KPI];

Left Join ([Overview])

Load
[KPI Key]
,sum([A.Case - Dengue])/$(vPopYear)*100000 as [Measurement]
,if( sum([A.Case - Dengue])/$(vPopYear)*100000 < [KPI.Target] , 1, 0) as [Green]  // Line Y

resident [A]
group by [KPI Key];

[Kushal_Chawda]

You have renamed the [KPI.Target] in line X so in Line Y you need to provide the renamed field name which is  "Target"

[KayPong]

I still get returned with an error that Target is not found when I changed Line Y to the following 

,if( sum([A.Case - Dengue])/$(vPopYear)*100000 < [Target] , 1, 0) as [Green] 

[Vegar · Accepted solution]

You are loading resident from table [A]. You give us no further info about this table. Have you checked that this table contains the KPI.Target field ? 

[KayPong]

Thanks Vegar.

Yes the problem lies in table A must contain KPI.Target which previously it was not there. I have used a temporary table to join it and now it works.