Performing calculation on a field in Load script

[#1]

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

[jonathandienst]

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

[miikkaqlick]

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

[#4]

Try to write the Last if statement in Preceding Load statement.