topic Re: IF statement in QlikView

IF statement

Wed, 23 Jul 2014 20:17:31 GMT

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.

Re: IF statement

Wed, 23 Jul 2014 20:21:42 GMT

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.

Re: IF statement

julian_rodriguez — Wed, 23 Jul 2014 20:21:47 GMT

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!

Re: IF statement

eduardo_sommer — Wed, 23 Jul 2014 20:22:01 GMT

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

Alternatively, you can use the alt() function:

alt(Budget, 0) as Budget

Eduardo

Re: IF statement

erivera10 — Wed, 23 Jul 2014 21:34:10 GMT

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.

Re: IF statement

eduardo_sommer — Wed, 23 Jul 2014 22:00:18 GMT

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

Re: IF statement

rwunderlich — Wed, 23 Jul 2014 23:26:26 GMT

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

Re: IF statement

erivera10 — Thu, 24 Jul 2014 13:45:28 GMT

You have reason,

Make this...

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

Re: IF statement

Thu, 24 Jul 2014 15:50:03 GMT

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

Re: IF statement

eduardo_sommer — Thu, 24 Jul 2014 16:06:06 GMT

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