How to achieve a Left Single Match Join in the TIBCO Spotfire Information Designer

How to achieve a Left Single Match Join in the TIBCO Spotfire Information Designer

In Spotfire Information Designer, the only options you see in a Join Element are the following: "Inner Join", "Left Outer Join", "Right Outer Join" and "Freehand". There is no direct way to achieve a Left Single Match Join. One workaround to achieve a Left Single Match Join in Information Designer is to create a Left Outer Join, then modify the SQL Query.

Consider the following tables named "A" and "B". 
 

Table - A

col1	col2
a	1
b	2
c	3
c	4

Table - B

col1	col3
a	6
c	8
c	9
d	10

If you implement a Left Single Match Join, the result table will be as follows.
 

Result

col1	col2	col3
a	1	6
b	2
c	3	8
c	4	8

  Here are steps to achieve the use case of implementing a Left Single Match Join in the Information Designer:

1. Create a Left Outer Join by clicking on the "Create Join" link in the "Start" tab of Information Designer, then add the columns that should be included in the join under "Join Columns" section of "Join" Tab (in this example, col1 from Table "A" and col1 from Table "B").

2. Create an Information Link by clicking on the "Create Information Link" button in the "Start" tab of Information Designer, add the columns to be included in the Information Link (col1 and col2 from Table "A", and col3 from Table "B"), and add the Join Element created in step 1, using the "Join Path" section of the "Information Link" Tab.

3. Now Edit the SQL that the current Information Link is generating and replace it with the below example query (ensure you update the example query to match your particular data source).

Modified Query:

select a.col1, a.col2, tb.col3 FROM 
  "sp70db1"."dbo"."A" a
 LEFT OUTER JOIN    
  ( select col1 as colb1, col3 from
   (
   select * , row_number() over (partition by col1 order by col1)    row
   from "sp70db1"."dbo"."B" B 
   )   as aa
   where row = 1) as tB
   on a.col1 = tb.colb1;

Doc: Editing the SQL of an Information Link

• https://docs.tibco.com/pub/sfire-analyst/10.10.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/id/id_editing_the_sql_of_an_information_link.htm