Nested if statements in load script, how to combine them into one field?

[pgkrsk]

Hey,

I have a table with fields such as Value, Balance, and Quantity, they are all numbers or strings of numbers. I want to achieve the following result inside load script:

if Value is less than zero and if Balance is not null, use Quantity as Deleted, otherwise if Value is less than zero and if Balance is null, use Balance instead as Deleted. So I would want one Deleted field as a result. I think this should be easy to do but I can't get this to work, here's what I'm trying to do but it doesnt work:

if (Value < 0,
if (IsNull(Balance),
Quantity)) as Deleted,
if (Value < 0,
if (not IsNull(Balance),
num(Balance) as Deleted

Thanks in advance!

[Or]

It looks like you're trying to create two fields rather than one...

I've gone with a NOT approach to match your order but you could just as easily write it with the THEN and ELSE reversed and remove the NOT.

If(Value<0,if(not isnull(Balance),Quantity,Balance)) as Deleted

Note that this will return null if Value is 0 or greater since you haven't provided any outcomes for that.