topic Setting values to fields based on values of other fields in QlikView

Setting values to fields based on values of other fields

Fri, 17 Jan 2014 09:48:11 GMT

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

...

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

Fri, 17 Jan 2014 09:56:04 GMT

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

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

Fri, 17 Jan 2014 10:21:21 GMT

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

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

Fri, 17 Jan 2014 10:27:36 GMT

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

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

jagannalla — Fri, 17 Jan 2014 10:31:49 GMT

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

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

Fri, 17 Jan 2014 11:50:20 GMT

Jagan,

Thank you. That works for me.