Ingvild Amaliksen Posted April 1, 2020 Share Posted April 1, 2020 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 Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted April 1, 2020 Share Posted April 1, 2020 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now