Handling NULL values for comparison when using .where()

[RussellJ]

I’m working on a solution which needs to lookup a unit price from an API call.  The JSON returned by the API call includes an array of Prices in the following format:

starting_quantity	ending_quantity	unit_price
1	100	9.99
101	200	8.99
200	NULL	7.99

I have a Quantity value that I want to use to lookup and return the relevant unit_price based on the “band” in which the Quantity sits.  I’m using WHERE and PLUCK to do this, and the formula is currently…

So, for example, looking up Quantity values of 1, 7 or 100 would all return a unit_price of 9.99 whereas looking up 110 would return a unit_price of 8.99.

My problem comes when I try to lookup a value which sits in a “band” where the ending_quantity is NULL.  For example a Quantity of 201 (or greater).

In this scenario Workato throws an error, reporting… "Error calculating input for field 'Unit_Price': The 'ending_quantity' is nil" ... which is indeed a true statement.

So, my question is how do I handle this situation?

This lookup routine will be invoked frequently, so minimising tasks is of the essence.

My current workaround is to use an additional Create List step to generate a new List, populating it with the same values as per the original Price array, but using a ternary operator when populating the ending_quantity field to substitute a NULL value with a bejllion instead (an impossibly high value in practice).

This only takes one extra task but it will add up over the volume of lookups expected for the initial bulk processing. (And yes I have checked that NULL in the ending_quantity is always used in the last row of such tables, to indicate an infinitely high value, rather than there being an additional alternative meaning for the use of NULL).

Am I missing a trick such that I can avoid this extra step and do something natively clever using WHERE?

Any suggestions would be appreciated...

[gary1]

Hey, can post the actual JSON (or a sample) and a better screenshot of your current formula? Either the image is really pixelated or I need bifocals, maybe both.

[gary1 · Accepted solution]

I read this over again and I don't think there's much else you can do with this approach. There's no inline substitution on reference values when using "where", so your approach to remap null to "gazillion" upstream is the best alternative. If you really want to save the task, you can drop this all into a Ruby or JS action to handle it all in one go.

[RussellJ]

Hi Gary

Sample JSON of the array of Prices looks like this..

And slightly bigger screenshot of the formula looks like this...

Appreciate your time (as always) and confirmation that there's nothing readily apparent as an alternative besides writing a code block.

Thanks

Russell