Setting values to fields based on values of other fields

[#1]

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],

...

[#2]

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

[#3]

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

[#4]

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]

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

[#6]

Jagan,

Thank you. That works for me.