[Q] NULL vs N/A

[fizvlad]

Hello!

I was trying to calculate following formula using Dentaku: IF(revenue - expense = NULL, expected_profit, revenue - expense). I created following Ruby script to test it:

require 'dentaku'

calculator = Dentaku::Calculator.new
values = {
  with_value: { revenue: 1000, expense: 200, expected_profit: 600 },
  with_nil: { revenue: 1000, expense: nil, expected_profit: 600 },
  without_value: { revenue: 1000, expected_profit: 600 }
}
expression = 'IF(revenue - expense = NULL, expected_profit, revenue - expense)'

values.each do |key, values|
  result = calculator.evaluate(expression, values)
  puts "#{key}: #{result.inspect}"

  begin
    calculator.evaluate!(expression, values)
  rescue
    puts "  !!! #{$!.class}, #{$!.message}"
  end
end

and got those results:

with_value: 800
with_nil: nil
  !!! Dentaku::ArgumentError, Dentaku::AST::Subtraction requires operands that respond to -
without_value: nil
  !!! Dentaku::UnboundVariableError, no value provided for variables: expense

I was expecting nil to act as N/A in Excel, but it actually behaves like NULL error. I've created similar calculations sheet for Excel to illustrate this:

I know that I can do IF(OR(revenue = NULL, expense = NULL), expected_profit, revenue - expense), but there might be much more complicated expressions with much more variables in use and it would be hard to write a check for each of them. I would like to get help on couple of questions:

1. Is there any way to make nil behave like N/A (i.e. most of operations should result in N/A rather than error)?
2. Is there anything similar to Excels IFERROR which I can use instead?

In case current behavior contains any bugs, I would be happy to help fixing them. Thanks in advance!

[rubysolo]

If you use the non-bang variant of evaluate, then with_nil and without_value both return nil. Is that helpful for your use-case?

[fizvlad]

If you use the non-bang variant of evaluate, then with_nil and without_value both return nil. Is that helpful for your use-case?

Sadly, no. I'm trying to make with_nil return value of expected_profit

[rubysolo]

Oh sorry, I should have read your comment more closely. 😞

I think we would need to substitute the nil value and allow evaluation to continue rather than throwing an error. Let me think about the best way to implement that change.

[BenjaminDazey]

I'm also very interested in a solution to this problem. We sometimes give a variable no value in the variable hash and would like to be able to evaluate it with something like ISNULL which we implemented.
calculator.add_function(:isnull, :numeric, ->(*args) { args.any?(&:nil?) })

We would then expect
IF(ISNULL(Q1), 46, 5)
to return 46 if it is null, but instead we get the error "No value provided for variables: q1"