Function for lockup value between tables

[Claudia Siano 2]

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.

e.g.

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.

[Fabian Duerr]

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.

[Claudia Siano 2]

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

[Fabian Duerr]

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

 

https://community.spotfire.com/questions/join-tables-date-range

 

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.

[Claudia Siano 2]

Hi Fabd

 

Unfortunately adding a column wouldn't work. I will investigate the fuzzyjoin function which i was not aware. 

 

thanks