hi,
Need advise on how to lookup a column from a table as below:-
Table = tableA
col1 col2 col3 col4 col5
------------------------------------------------------------------
A a1 <100cc 0 99
A a1 100cc to 200cc 100 200
A a1 >200cc 201 9999
A a2 <5months 0 4
A a2 5months to 10months 5 10
A a2 >10months 11 99
How to lookup for col3 in transformer stage?
if given input col1 = A, col2 = a1, measure = 50;
expected result = <100cc
via SQL:-
select col3 from tableA where col1 = A and col2 = a1 and col4 <= 50 and col5 >= 50
Lookup thru ODBC stage? or write the table to Hash File and retrieve the value thru routine?
How to lookup a field with conditional field from input
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 41
- Joined: Fri Jun 04, 2004 5:52 am
- Location: Bangalore
There are similar queries raised here before....
You can achieve this through lookup using ODBC or Universe Stage.
Use Col1 & Col2 as Key fields in the lookup and enable "Reference link with multi row result set" in Transformer Stage Properties -> Inputs. This will return you all the matching records from the lookup for the keys (in this case, three records). And you can define your logic in StageVariabe to pick up the correct value. In Stage Variable, you can give something like:
If Not(InputLinkName.NOTFOUND) And col4 >= InputLinkName.FieldNm and col5 <= InputLinkName.FieldNm Then .... End.
You can achieve this through lookup using ODBC or Universe Stage.
Use Col1 & Col2 as Key fields in the lookup and enable "Reference link with multi row result set" in Transformer Stage Properties -> Inputs. This will return you all the matching records from the lookup for the keys (in this case, three records). And you can define your logic in StageVariabe to pick up the correct value. In Stage Variable, you can give something like:
If Not(InputLinkName.NOTFOUND) And col4 >= InputLinkName.FieldNm and col5 <= InputLinkName.FieldNm Then .... End.
Like Craig put it, a search would have yielded a wealth of information on range lookups.
There was one that I posted which deals with the use of UV stage to perform one.
viewtopic.php?t=111293
A second option would be to use temp table in your database where the join is performed based on the sql that you provided.
There was one that I posted which deals with the use of UV stage to perform one.
viewtopic.php?t=111293
A second option would be to use temp table in your database where the join is performed based on the sql that you provided.
gateleys
HI, thanks so much. It's working.
But, my problem now is :-
if input got 100 rows, with the multi row result set, it cause the output row more than 100 rows. As the input maynot have result from the lookup, how to restrict the output row to only contains the original 100 rows with result from lookup?
But, my problem now is :-
if input got 100 rows, with the multi row result set, it cause the output row more than 100 rows. As the input maynot have result from the lookup, how to restrict the output row to only contains the original 100 rows with result from lookup?
Check the status of your lookup and constrain your output to only those source records where the lookup succeeded. Link Variables are used for that:
Code: Select all
Not(LookupLink.NOTFOUND)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
With a non-exact-match lookup you are destined to receive more than one row returned. Each will satisfy your match criteria. I can only answer your question with a question - which of them do you want to keep?
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.