Range lookup in DQC


(Kalicharan Khetwal) #1

Hi Team,

I have an input inp1 (date) for search input(external source column value) and using that input, I need to get a column X(date) from a db table which will basically fall under column A(date) and column B (date) within same table. So I need to lookup my DB table using inp1 and use the column A and column B as a range value to get column X.
Can you please help me out on this?

Thanks,
Kalicharan


(Kalicharan Khetwal) #2

Note: DB is posgreSQL


(Maksim Zhelyazkov) #3

Hello Kalicharan,

Can you please clarify If I understood you correctly:

  • You have a posgreSQL DB, in which there is a table with 3 columns: col1(date), cola(date), colb(date);
  • You wish to create a lookup with a columnx, which checks if the dates in col1 are between cola and colb.

If this is the case you can create a plan, which looks like this:

  • plan
  • Alter Format step adds a columnX;
  • In Lookup Builder step in the Additional Columns field write colX and in the Expression field the following expression: col1>=safemin(cola,colb) AND col1<=safemax(cola,colb);
  • After you run the plan in the newly created lookup you can see colx – in it the col1 dates that are in the range of cola and colb are shown as ‘true’ and the col1 dates outside of this range are ‘false’;

Please, let us know if this was what you were looking not. If not, you may change the plan a bit according to your needs. If you will face any issues, please, let us know.

Regards,

Maksim