I have a requirement where I have to perform calculation on a field created within the load script and that is not in the source. Would like to get some guidance on how to do this:
LOAD Project,
...
Energy,
Irr1,
Irr2,
ModTemp1,
ModTemp2,
if (IsNull ([Irr1]) and IsNull ([Irr2]), '', if (IsNull ([Irr1]), [Irr2], [Irr1])) as [MedIrr],
(ModTemp1 + ModTemp2)/2 as [MedianModtemp],
if (IsNull ([Energy]) and IsNull (Previous([Energy])), '', if (IsNull ((MedIrr*MedianModtemp)),
(MedIrr*MedianModtemp), '')) as [FinalIrr],
...
I am getting the error in the final If statement: Field not found for 'MedIrr' and 'MedianModTemp' fields (as they are not in the source, but generated within this load script)
Jonathan already described one way to perform that.
Another way:
Replace, in final If-statement, MedIrr with MedIrr calculation and MedianModtemp with MedianModtemp calculation:
LOAD Project,
...
Energy,
Irr1,
Irr2,
ModTemp1,
ModTemp2,
if (IsNull ([Irr1]) and IsNull ([Irr2]), '', if (IsNull ([Irr1]), [Irr2], [Irr1])) as [MedIrr],
(ModTemp1 + ModTemp2)/2 as [MedianModtemp],
if (IsNull ([Energy]) and IsNull (Previous([Energy])), '', if (IsNull (( if (IsNull ([Irr1]) and IsNull ([Irr2]), '', if (IsNull ([Irr1]), [Irr2], [Irr1])) * ((ModTemp1 + ModTemp2)/2))),
((if (IsNull ([Irr1]) and IsNull ([Irr2]), '', if (IsNull ([Irr1]), [Irr2], [Irr1])))*((ModTemp1 + ModTemp2)/2)), '')) as [FinalIrr],
