Closest value for a given ID two tables

[Ingvild Amaliksen]

Hi,

I have two tables in spotfire. Im trying to find X & Y at the closest value to a certain value in another table for a given ID. I think it is easiest explained in an example.

I want to find X and Y at the value of MD in table 2 which is closest to the TD value in table 1.

The result should be as Result 1 or Result 2. Any advice on how to code this

 

Table 1:

 

 

 

ID

TD

 

 

G-4

2080

 

 

G-4

2080

 

 

G-4

2080

 

 

G-4

2080

 

 

G-10

1530

 

 

G-10

1530

 

 

G-10

1530

 

 

G-10

1530

 

 

 

 

Table2

 

 

 

ID

X

Y

MD

 

 

G-4

60340503

403043

2000

 

 

G-4

60340509

403046

2030

 

 

G-4

60340509

403046

2066

 

 

G-4

60340512

403054

2084

 

 

G-10

6035000

403000

1500

 

 

G-10

6035004

403007

1543

 

 

G-10

6035007

403008

1572

 

 

G-10

6035014

403014

1617

 

 

 

 

Result :

Either of these would work

 

 

Result 1:

 

 

 

id

TD

X

Y

 

 

G-4

2080

60340512

403054

 

 

G-4

2080

60340512

403054

 

 

G-4

2080

60340512

403054

 

 

G-4

2080

60340512

403054

 

 

G-10

1530

6035004

403007

 

 

G-10

1530

6035004

403007

 

 

G-10

1530

6035004

403007

 

 

G-10

1530

6035004

403007

 

 

 

 

Result 2:

 

 

 

id

TD

X

Y

 

 

G-4

2080

60340512

403054

 

 

G-10

1530

6035004

403007

[Khushboo Rabadia]

You can insert TD column in other tableand then apply transformations to filter out unwanted rows.

First import data table 1

Then import data table 2

Then insert TD column in table 2 matching on ID and left single match join

Then add transformations as shown below:

1. Select Data > Add...

Source: Data loaded from clipboard.

Data loaded at: 4/1/2020 5:29 PM

Data was added as a new data table

2. Select Data > Add...

Source: Data table from current analysis

Data table: Data Table

Update behavior: Automatic

Data loaded at: 4/1/2020 5:31 PM

Data was added as new columns in data table 'Data Table (2)'

Matching behavior: Tries to match the specified columns when data is loaded

Matched columns: ID ID

Added columns: TD

Ignored columns: (None)

Join method: Left single match join

Treat empty values as equal: No

3. Select Data > Transform data...

Added transformations

Transformation name: Calculate new column

Column name: difference

Expression: Abs([MD] - [TD])

 

Transformation name: Calculate new column

Column name: Close Indicator

Expression: case when [difference]=Min([difference]) OVER ([iD]) then "Y" else "N" end

 

Transformation name: Filter rows

Expression: [Close Indicator]="Y"

 

Resulting table will be as shown in Result 2