Pentaho compare values from table to a number from REST api

Question

I need to make a dimension for a datawarehouse using pentaho. I need to compare a number in a table with the number I get from a REST call. If the number is not in the table, I need to set it to a default (999). I was thinking to use table input step with a select statement, and a javascript step that if the result is null to set it to 999. The problem is if there is no result, there is nothing passed through. How can this be done? Another idea was to get all values from that table and somehow convert it to something so I can read id as an array in javascript. I'm very new to pentaho DI but I've did some research but couldn't find what I was looking for. Anyone know how to solve this? If you need information, or want to see my transformation let me know!

Steps something like this:

1. Load number from api
2. Get Numbers from table

3. A) If number not in table -> set number to value 999

   B) If number is in table -> do nothing

4. Continue with transformation with that number

I have this atm:

But the problem is if the number is not in the table, it returns nothing. I was trying to check in javascript if number = null or 0 then set it to 999.

Thanks in advance!

Answer 1

Replace the Input rain-type table by a lookup stream.

You read the main input with a rest, and the dimension table with an Input table, then make a Stream Lookup in which you specify that the lookup step is the dimension input table. In this step you can also specify a default value of 999.

The lookup stream works like this: for each row coming in from the main stream, the steps looks if it exists in the reference step and adds the reference fields to the row. So there is always one and exactly one passing by.