Conditional Look up

[bapajju]

Hi all,
I am using a HASH file for look up. The HASH file has fields as A,B, C and all the fields are key fields. My input record has fields as C1,C2 and C3. I need to do the look up as mentioned below:

Lookup will be successful if:

C1 matches with A
AND
(C2 matches with B when C3='LINE'
or
C2 matches with C when C3='POINT')

please note C3 can be either LINE or POINT.

Please let me know if we can achieve this in a single transformer. Else please let me know how to achieve this.Please.

Thanks in Advance

[denzilsyb]

This is how i would try it:

In the transform (left pane) link the columns C1 = A, C2 = B, C3 = C.
In the transform (right pane) write a constraint that satisfies the IF statement you gave:

C1 matches with A
AND
(C2 matches with B when C3='LINE'
or
C2 matches with C when C3='POINT')

something like...

Code: Select all

INPUT.C1 = LOOKUP.A and 
 ( ( INPUT.C2 = LOOKUP.B and INPUT.C3 = 'LINE' ) or 
   ( INPUT.C2 = LOOKUP.C and INPUT.C3 = 'POINT' ) 
 )

the IF statement might need a little work.

You could alternatively have two outputs from the transformer satisfying

Code: Select all

(1) INPUT.C1 = LOOKUP.A and ( INPUT.C2 = LOOKUP.B and INPUT.C3 = 'LINE' )

or

Code: Select all

(2) INPUT.C1 = LOOKUP.A and ( INPUT.C2 = LOOKUP.C and INPUT.C3 = 'POINT' )

and then merge the output at a later stage.

[chulett]

I really don't think this can work. I'd need to build a test job to confirm but it just doesn't seem right.

You have to supply all three keys to the lookup. Either all three match or the lookup fails. So, when C2=B then C2<>C and vice-versa.

My first thought was something I've done before, two separate lookups to the same hash. Check the success of one then the other and take whichever one (if either) is successful. Again, the 'three keys problem' makes that not work.

Is there any way you can build two hash files from your source data? One for the LINEs and another for the POINTs, each with only two keys?

[chucksmith]

What are the range of values for C2 and C3?

Possibly a denormalized hash file with a primary key of C1 and multivalue arrays storing the C2 / C3 values may reduce this problem to a single lookup with some additional logic to select the correct value.

[bapajju]

Thanks dnzl. I will try the same. But still I have a doubt whether that will work.As Chulett the look up will work as Match or Mismatch. Still I will try your suggestion. Thanks a lot.

[andru]

You have to create two hash files. One with keys A and B and other with Keys A and C.

Do both the lookups unconditionally.

In your transformer use a derivation as
If C3='LINE' and Lkp1 successful then LKP1.value
else If C3='POINT" and LKP2 successful then LKP2.value

Hope this helps.

[ray.wurlod]

Hashed file lookups with hashed file stages involve ONLY the "=" operator.

End of story.

The rvalue (reference key expression value) given is passed through the hashing algorithm for the hashed file, and the location for the record thus determined. The record is then either retrieved or reported as not found.

If you want to do lookups against hashed files using other operators than "=" you will need to use a UniVerse (UV) stage, in which you can create user-defined SQL.

Performance won't be nearly as good, as the UV stage can not take advantage of memory caching. Its performance can be improved by indexing the hashed file on the constraining columns.

[nkln@you]

For using l look-ups other than = , i tried with Universe stage, but i am stuck.

My main source table has fields A.SALES_REP_CDand A.BOOKED_DATE.

My look-up has fields B.SALES_REP_CD,B.START_DATE,B.END_DATE ,B.SALES_REP_KEY.

I need to " select max(B.sales_rep_key) FROM B WHERE A.SALES_REP_CD=B.SALES_REP_CD AND
A.BOOKED_DATE>=B.START_DATE AND
A.BOOKED_DATE <=B.END_DATE

B.SALEs_REP_KEY is the o/p of the look-up.

In UV stage i used user-generated query, but how do i compare fields of B with that of fields in Transformer (coming from A).

table A contains nearly 3 million records.

Should i use hash stage in between UV stage and Transformer?

Pls help me to sort out this problem

[rumu]

For using l look-ups other than = , i tried with Universe stage, but i am stuck.

My main source table has fields A.SALES_REP_CDand A.BOOKED_DATE.

My look-up has fields B.SALES_REP_CD,B.START_DATE,B.END_DATE ,B.SALES_REP_KEY.

I need to " select max(B.sales_rep_key) FROM B WHERE A.SALES_REP_CD=B.SALES_REP_CD AND
A.BOOKED_DATE>=B.START_DATE AND
A.BOOKED_DATE <=B.END_DATE

B.SALEs_REP_KEY is the o/p of the look-up.

In UV stage i used user-generated query, but how do i compare fields of B with that of fields in Transformer (coming from A).

table A contains nearly 3 million records.

Should i use hash stage in between UV stage and Transformer?

Pls help me to sort out this problem

Hi,
In this case you can not use Hashed file stage since in hashed file stage you can not use any operator other than "=".Use UV stage,make B.SALES_REP_CD,B.START_DATE,B.END_DATE as key then switch to user defined qry ,your query wud look like-
select max(B.sales_rep_key) FROM B WHERE B.SALES_REP_CD=?AND
B.BOOKED_DATE>=?AND
B.BOOKED_DATE <=?

In transfor mer stage make look up on A.SALES_REP_CD,A.BOOKED_DATE and A.BOOKED_DATE and drag B.sales_rep_keyto ur desired output column.
Hope this will work.
To all Ds gurus pls correct me if I am wrong.