topic Re: Performing calculation on a field in Load script in QlikView

Performing calculation on a field in Load script

Mon, 13 Jan 2014 13:29:36 GMT

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

Re: Performing calculation on a field in Load script

jonathandienst — Mon, 13 Jan 2014 13:33:15 GMT

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

Re: Performing calculation on a field in Load script

miikkaqlick — Mon, 13 Jan 2014 13:56:11 GMT

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

Re: Performing calculation on a field in Load script

Mon, 13 Jan 2014 15:41:28 GMT

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