Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Setting values to fields based on values of other fields

Hi,

I have 3 fields and I want to reset all the 3 fields to 0, even if one of them is 0. Can I set the fields in LOAD statement like this? I did not get any error, but did not get the desired effect either.

Thanks

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, [MedianModTemp] = 0)

;

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, [MedianIrr] = 0)

;

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, [MedianIrr] = 0)

;

LOAD Project,

...

     Energy,

     Irr1,

     Irr2,

     ModTemp1,

     ModTemp2,

     Energy - (if (IsNull (Previous(Energy)),0,Previous(Energy))) as [Energy Diff],

     (if (IsNull(ModTemp1),0,ModTemp1) + if(IsNull(ModTemp2),0,ModTemp2))/2  as [MedianModTemp],

     (if (IsNull(Irr1),0,Irr1) + if(IsNull(Irr2),0,Irr2))/2 as [MedianIrr],

...

5 Replies
Not applicable

Re: Setting values to fields based on values of other fields

Hi,

Can you please share your detail requirement or Sample QVW??

From the above code, what value is expected when if conditions is true is not mentioned and e1 field is not aliased.

-Shruti

Not applicable

Re: Setting values to fields based on values of other fields

Hi Shruti,

I have in a spreadsheet values of energy generated in 15 minute intervals. I also have other parameters like Irradiance, Module Temp etc. I will be calculating operating performance based on these values. If one of the values is 0 (for any reason), I want to ignore all 3 values, otherwise I will get an invalid performance value. I want to reset the values for all 3 fields (Energy Diff, MedianmodTemp and MedianIrr) in such cases.

Thanks

Srikanth

Not applicable

Re: Setting values to fields based on values of other fields

Hi,

You can LOAD into another table or when reading the first time your file:

LOAD

if(A=0 or B=0 or C=0, 0, A) as A,

if(A=0 or B=0 or C=0, 0, B) as B,

if(A=0 or B=0 or C=0, 0, C) as C

FROM xxx (or Resident xxx)

if() returns a value that you will affect to your field.

Fabrice

jagannalla
Valued Contributor III

Re: Re: Setting values to fields based on values of other fields

Hi,

Check this

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[MedianModTemp]) as [MedianModTemp]

;

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[MedianIrr]) as [MedianIrr]

;

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[Energy Diff]) as [Energy Diff]

;

I think you are loading from one table. if I'm correct you can load all statements at a time

LOAD

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[MedianModTemp]) as [MedianModTemp],

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[MedianIrr]) as [MedianIrr],

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[Energy Diff] ) as [Energy Diff]

from table;

Thanks,

Jagan

Not applicable

Re: Re: Setting values to fields based on values of other fields

Jagan,

Thank you. That works for me.

Community Browser