Merge multiple Tables

[Jay Wu]

I was wondering if you know there is any way that Spotfire can do the following

 

Table 1 (main table) in the attachment is the main table, Table 2 is the supporting table. Is there any method that Spotfire can transform and combine those 2 tables into the tab Goal.

[Fabian Duerr]

There is no built-in join method for this operation. But you can solve this with a data function. Both tables would be input to your function and the output could be a new table. Your function has to loop thru every row in "table 1", get the ID and value and then check in "table 2" in the row with this ID for the smallest difference to the value. The resulting vector can then be added as column to "table 1" to create your output table "goal".

Alternatively you could transpose your "table 2" and use some kind of fuzzyjoin (R package) to get the same result.

Do you have experience with data functions or do you need further assistance