cookiemnstr247 cookiemnstr247 - 2 months ago 17
SQL Question

Spotfire Join to unmatched records

I have a table that I am trying to match against itself and create a record when the primary key does not match. Here is an example of the table

Location Value
A 5
B 10
C 15


I want to obtain the following table

Location Value Location(2) Value(2)
A 5 B 10
A 5 C 15
B 10 A 5
B 10 C 15
C 15 A 5
C 15 B 10


I have duplicated the first table and tried the various joins but I cannot get the result. Could anyone provide a suggestion on how this can be performed?

Answer

Looks like you want the vector product i.e. outer join. But to have a join you need a column with rows matching. A trick here would be to create a new column with just a single value like "1" for all of the rows (use "Insert Calculated Column" - be sure to freeze the column so you can join to it later). Then do a full outer join of that table with a copy of itself (use the "Insert Columns" feature for the join) using the column with that dummy column as the key field. You will then get the combinations you showed above, but it will also have rows where the keys matched.

To remove the matches, you can easily create a new column with an expression testing if the primary keys match like:

if([Location]=[Location(2)],"Match","NoMatch")

Then filter to the matching rows and delete if you don't want them in the data set.

You can certainly ask Spotfire questions here, but you can also try the Spotfire section of the TIBCO Community here:

https://community.tibco.com/products/spotfire