topic Re: Data Load Editor Syntax for Calculations in App Development

Data Load Editor Syntax for Calculations

AndyQuirin1 — Wed, 08 Mar 2023 04:00:39 GMT

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

Re: Data Load Editor Syntax for Calculations

MayilVahanan — Wed, 08 Mar 2023 05:39:40 GMT

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'

Re: Data Load Editor Syntax for Calculations

marcus_sommer — Wed, 08 Mar 2023 12:41:43 GMT

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.