need all matching records from Lookup table not max or min

[subbuch]

i have one requirement where i have to pull only one record from look up table where we have multiple matches, but based on one more columns

below is the example

code text active

13 ha 0

13 ra 0

13 sa x

14 CR 0

my output should be

13 sa

14 CR

my ouput should be sa and CR my lookup condition is code from source= code from look up table and pull Text

if we have only one match we should pass that, and when we have multiple matches we should get only one record where Active status is X. Any suggestions

[vijayb · Accepted solution]

Hi Venkat,

It can be done in other way instead of lookup by following below steps.

• Do a left join with source directly with lookup table instead of using lookup function
• Spilt the data based on active and inactive into two tables
• Delete the records from inactive which are in active based on code using script
• Merge the two tables data into one table

Thanks

Vijay

[subbuch · Accepted solution]

Hi Vijay

Thanks for your quick response i have implemented the similar logic but not joins,

1. Filter the records x and null into 2 different tables

2.using the script delete the records which are already present in X table

3. Merge both the tables at the end.

4.after this i am doing look up from source table and pulling the records.

Even your approach is good to achieve the desired result.