IF statement

[#1]

Hi All,

I want to write an IF statement in the load script and not sure how to do it.

In plain English, if the amount is Budget and it is null then make it 0. If the amount is actual, use the amount.

Can someone please guide me on how to write this statement?

Thank you.

[#2]

Lets say Amount is the field with numbers and is there another field that says whether its Budget or Actual(how do you define that). Lets say that field is Flag

If so

If(Flag='Budget' and Isnull(Amount),0,if(Flag='Actual',Amount)) as Final_Amount

Edit: What about if its Budget and not null? How do you wanna handle that.

[julian_rodriguez]

If Budget is null or is zero, then Budget will be zero, else will be the value of Budget.

LOAD

     If(IsNull(Budget) = -1 Or Budget =0, 0, Budget) AS Budget

FROM

     YourFile.qvd (qvd);

Regards!

[eduardo_sommer]

if (isNull(Budget), 0, Budget) as Buget

Alternatively, you can use the alt() function:

alt(Budget, 0) as Budget

Eduardo

[erivera10]

Hi rsolomon,

I read good reviews, like eduardo but if you want another alternative can operate where, for example

Where isnull(Budget) = 0;

Erick Rivera S.

[eduardo_sommer]

Hi Erick,

The requirement from rsolomon's question was that the Budget field should change to zero when it was null. With your suggestion, the rows with null Budgets would be ignored.

Eduardo

[rwunderlich]

Alt() is probably easiest.

I'm not clear from your question if Budget is a field, or if Budget is a type stored in another field and Amount is the field.

If Budget is a field:

alt(Budget,0) as Budget

If a type, and it's ok to turn all null Amounts to 0:

alt(Amount,0) as Amount

If you must also include a "Budget" type test then:

if(Type='Budget', Alt(Amount,0), Amount) as Amount

-Rob

[erivera10]

You have reason,

Make this...

if(isnull(Budget), 0,Budget) ;

[#9]

Thank you all for responding with solutions.

My column name is Type that has 2 options: Actual and Budget. Can anyone tell me what I did wrong in the statements below? Did I misunderstand something?

Thanks.

I tried 3 different ways and it did not work:

IF(IsNull(Type), 0, Type) as Type

If(Type ='Budget','0',Type) as Type

if(Type='Budget', Alt(Type,0), Type) as Type

[eduardo_sommer]

Hi,

What's wrong is that you are assigning the field Type as if it were the value. Which field actually contains the value? Type distinguishes between Budget and Actual, but does not have the value you want to assign.

Suppose your value field is called Value, your line would be:

If (Type='Budget', Alt(Value, 0)) as Value

Eduardo