Lookup condition -- Is there an alternative way to handle

devidotcom · Post by **devidotcom** » Sat Jun 09, 2007 2:52 am

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.

keshav0307 · Post by **keshav0307** » Sat Jun 09, 2007 3:22 am

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

devidotcom · Post by **devidotcom** » Sat Jun 09, 2007 3:37 am

[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.

keshav0307 · Post by **keshav0307** » Sat Jun 09, 2007 9:53 am

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

ray.wurlod · Post by **ray.wurlod** » Sat Jun 09, 2007 6:48 pm

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.

devidotcom · Post by **devidotcom** » Sun Jun 10, 2007 10:06 pm

[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?

devidotcom · Post by **devidotcom** » Sun Jun 10, 2007 10:10 pm

[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.