Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Performing calculation on a field in Load script

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

3 Replies
jontydkpi
Not applicable

Re: Performing calculation on a field in Load script

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
miikkaqlick
Not applicable

Re: Performing calculation on a field in Load script

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

Not applicable

Re: Performing calculation on a field in Load script

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