Pentaho Spoon Database Lookup, how to compare between dates

Question

I'm using Pentaho Spoon to develop an ETL, I need to make a serie of lookups taking Extract's denormalized data and retrieving Dimension's PKs.

One of these dimensions has starting and ending date for the vigency of a record. So, I need to lookup the table using Extract's reference date over Dimension's starting and ending dates.

The issue is that Spoon seems to only offer between comparation from Dimension to the source. It means that Field 1 and Field 2 are only available for source data, not for the lookup table.

Is there a way to invert it?

Answer 1

If I understood your question correctly, you can split your condition into two conditions instead, referencing the same source data field but with different lookup fields.