Trouble with left outer join

[Christine Gopon]

I'm a fairly new Spotfire user, so this may just be a a simple fix.

I have two large datasheets I am trying to join using a left outer join. While Spotfire will successfully match the appropriate columns, it will not populate the added columns with data from the second spreadsheet.

Perhaps the issue is that the Matched Columns have multiple row entries in Table 1, and only one row entry in Table 2 I have included an image of an example of what I am trying to acheive.

I have also tried linking the data tables using the matching column, but was unsuccessful.

Any suggestions

[Fabian Duerr]

The Left Outer Join is the correct method for what you want to achieve. That's fine.

 

Can you give more details how you perform this join Do you use "Add Columns" Are there multiple column matches in your table What are the data types of your columns If you have strings for example, please ensure that there are no leading or trailing spaces in one of the columns.  ("aaa" will not join to "aaa "). If this is the case, you can use the trim() function prior to your join. 

[Ghantasala Krishna Kowsik]

Try this -> Add columns -> Settings for added columns (Match Columns: Table 1 and Table 2) -> Transform Data -> Pivot -> Row identifiers (Column - A)-> Values (Column - C) -> Ok

[Christine Gopon]

Thanks for your response. I have an update:

 

There was, in fact, a leading space in one of my string columns, so your suggestion to use the trim function was spot on, thank you. 

 

However, that did not entirely solve my problem. Apparently I had too many rows in the "new data" data table without equivalent matches in the original data table (about 2000 rows total, only about 80 of which had matches). I did a row filter transformation on the new data table to slim it down, and Spotfire was able to process the join after that.

 

Thanks again!

[Fabian Duerr]

Perfect! Thank you for confirming the "space issue". :)

 

If you only want to keep matches from both tables you can also use the inner join method. The various join methods are really well explained in the join menu (Spotfire 10 +).