DSXchange

Consider a scenerio, where I have to perform lookup on a reference table STROE based on a column say STORE_I and also check for the condition where BTCH_CRTE_TS is between ETL_PRCS_EFF_TS and ETL_PRCS_END_TS. ETL_PRCS_EFF_TS and ETL_PRCS_END_TS are obtained from the reference table and BTCH_CRTE_TS from the source.

Using a lookup stage it is possible only to perform a lookup on STORE_I and then fetch ETL_PRCS_EFF_TS and ETL_PRCS_END_TS, following which we need to use a transformer stage to find out if BTCH_CRTE_TS is between ETL_PRCS_EFF_TS and ETL_PRCS_END_TS, then choose the appropriate row and reject the others.

Could you help me find a solution where only a single stage could help us out as we have huge amount of data we are working with approx 25 million per day. Also have 3 such reference tables with the same condition to be used. In such a case we would need to use 3 lookups and 3 transformers which would be costly for our job.

if you use sparse lookup, it can be done in a single lookup stage.

[quote="keshav0307"]if you use sparse lookup, it can be done in a single lookup stage.[/quote]

Could you elobrate... how is it possible? If you use a sparse lookup how will I check the condition as we would not be able to use constraint.

use user defined sparse query, and and have reject link for the lookup not found..

You can check the condition in the "rules" part of the Lookup stage. If the lookup fails there are four possibilities; Continue, Drop, Fail or Reject. If the condition to undertake the lookup in the first place is not met, the same four possibilities obtain.

[quote="ray.wurlod"]You can check the condition in the "rules" part of the Lookup stage. If the lookup fails there are four possibilities; Continue, Drop, Fail or Reject. If the condition to undertake the lookup in the ...[/quote]

Thank you for the reply.
Here the issue is with checking two columns from reference table ETL_PRCS_EFF_TS and ETL_PRCS_END_TS if it has BTCH_CRTE_TS soruce column between them.
We would not be able to use two reference columns in the "rules" section.
Hence need an aditional transformer. Another way out?

[quote="keshav0307"]use user defined sparse query, and and have reject link for the lookup not found..[/quote]

Thanks Keshav.
Was able to write the user-defined query and work appropriately.