Jump to content

Function for lockup value between tables

Claudia Siano 2

Recommended Posts

i have a very similar issues to this old question and i was hoping someone could help me.

I tried to suggested answer but they di dnot work for me

Table1.id and table1.value are columns , same as Table2.lowvalue and Table2.highvalue


I am searching for an example lookup data function in R or Python where the lookup value is within a range of values.


Table1: id, value

LookupTable2: id, lowvalue, highvalue, category

input:[Table1.id] , [Table1.value]

where[Table1.id]=Table2.id] AND [Table1.value] >= [Table2.lowvalue] AND [Table1.value] < [Table2.highvalue]

return: [Table2.category]

Hope this makes sense.

Link to comment
Share on other sites

What former question are you referring to Could you share the link


There are multiple ways to get what you are asking for.


You can either insert columns from one table to another and then use a filter (basically a table merge/join).


Or you could link the tables by the column [id] and then use a common filter or data limiting options.


Writing a data function for this is possible but not necessary.

Link to comment
Share on other sites

Just to clarify .

I have two tables.

Table 1 :

Well Name Depth

Well A 600

Well A 800

Well A 950

Well B 300

Well B 920

Well B 222


Table 2 :

Well Name top base (2) Zone

Well A 400 600 Q1

Well A 600 800 A

Well A 800 950 B

Well A 950 1150 C

Well A 1150 1150 D

Well A 1150 2000 E

Well A 2000 2000 F

Well A 2000 2000 G

Well A 2000 2100 H

Well A 2100 3000 I

Well A 3000 3000 J

Well A 3000 3100 L

Well A 3100

Well B 300 450 Q1

Well B 450 550 D

Well B 550 700 E

Well B 700 800 F

Well B 800 1100 G

Well B 1100 1200 H

Well B 1200 1200 I

Well B 1200 1300 J

i would like a function to find out what zone is for every sample in table 1 based on the depth.

So every table1.well match to table2.well and when table1.top

Link to comment
Share on other sites

You, as many others, are looking for a fuzzyjoin. Please have a read here:




One way to get a similar result without any data function is using a left join of table2 on table1 (matching column = Well Name). This will of course increase the number of rows of table1. But then you can limit your table either with a filter using a calculated column that checks if depth >= top and depth < base or with a data limiting expression using the same condition:


[Depth] >= [top] and [Depth] < [base(2)]


But please keep in mind that with this workaround you have additional rows in your table. They are just filtered. They may cause issues if you do further analysis or calculated columns. If you just want to display the data, this solution will work fine for you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...