How to lookup a field with conditional field from input

ysweec · Post by **ysweec** » Wed Sep 05, 2007 3:14 am

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?

tombastian · Post by **tombastian** » Wed Sep 05, 2007 4:45 am

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.

chulett · Post by **chulett** » Wed Sep 05, 2007 7:16 am

You should be able to Search here for 'range lookup' and find several conversations on this topic.

gateleys · Post by **gateleys** » Wed Sep 05, 2007 8:05 am

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.

ysweec · Post by **ysweec** » Thu Sep 06, 2007 1:07 am

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?

chulett · Post by **chulett** » Thu Sep 06, 2007 7:53 am

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)

ray.wurlod · Post by **ray.wurlod** » Thu Sep 06, 2007 3:11 pm

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?