Data Load Editor Syntax for Calculations

[AndyQuirin1]

Hi All,

I'm trying to do a couple simple conditional calculations in the data load editor.
They work for some rows, but I'm finding if any of the values of calculation is blank / null, the whole calculation comes back as 0. 

Bookings:
Load
Deal_ID,
IF([Deal Status]='won',[Deal Total Value]+[Rebate],0) as 'Deal Bookings'
,IF(Status='won',Value_Of_Deal+Rebate-CostofGoods-,0) as 'Deal GM'
RESIDENT [Deals];

Do I need to be casting the values as a certain data type? Or trying to convert the nulls?
Is there a better syntax I should be considering?

Thanks,
Andy

[MayilVahanan]

Hi

Are you looking, if Deal status is either WON or Null, do you want to do the logic instead of 0?

If so, try like below

If(Coalesce([Deal Status, 'won')  = 'won', [Deal Total Value]+[Rebate],0) as 'Deal Bookings'

[marcus_sommer · Accepted solution]

Beside catching NULL within the status you may also consider to use rangesum() instead of +- operators between the fields because each non-numeric value will cause NULL as result.