Conditional Look up
Moderators: chulett, rschirm, roy
Conditional Look up
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
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
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:
the IF statement might need a little work.
You could alternatively have two outputs from the transformer satisfying
or
and then merge the output at a later stage.
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:
something like...C1 matches with A
AND
(C2 matches with B when C3='LINE'
or
C2 matches with C when C3='POINT')
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' )
)
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' )
dnzl
"what the thinker thinks, the prover proves" - Robert Anton Wilson
"what the thinker thinks, the prover proves" - Robert Anton Wilson
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
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.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Aim high
Hi,nkln@you wrote: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
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.