Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Thanks for your time
Sri
Sri
Same solution as before - preceding load:
LOAD *,
if (IsNull ([Energy]) and IsNull (Previous([Energy])), '', if (IsNull ((MedIrr*MedianModtemp)),
(MedIrr*MedianModtemp), '')) as [FinalIrr
;
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],
HTH
Jonathan
Hi!
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],
Br,
Miikka
Try to write the Last if statement in Preceding Load statement.