Table transformer lookup should not show rows from table 2 if there are no common lookup data

[Noori Fathima]

We are using Table Transformer to lookup 2 tables. 

Table T1 : Project, Manager, Lead

Table T2 : Project, Created, Key, etc. (required fields from JIRA)

The lookup is done using the below query. 

SELECT T2.'Created',T2.'Key', T2.'Project',T2.'Summary',T2.'P' as 'Priority',T2.'Status',T1.'Manager',T1.'Lead' FROM T1 OUTER JOIN T* ON T1.'Project' = T*.'Project' order by T2.'Created'

The idea is to have 1 lookup table T1 with all the owners at different levels for all JIRA boards that we monitor. Say we monitor 10 JIRA boards, we will have 10 rows with all the data filled in the table. 

The issue is that say 'Project A' has data in T1 but there are no open JIRA issues that show up in T2 for the 'Project A' , then the lookup is adding an extra row in the transformed table with T1 data and T2 related data is empty. This is confusing to us and would like to not include this in our view. 

As part of the Lookup, it's okay to show T2 data in the transformed table with missing T1 data. This helps us to add the missing Project and owner in T1. 

But vice versa gives a wrong view, because the count of the transformed table rows is based on the total jira issues filtered. Kindly help in excluding the T1 data in the transformed table for Projects that are not part of T2.

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Noori Fathima ,

Please try to replace the "OUTER JOIN" expression in your query with the "LEFT JOIN" expression. It should help your case.

Or you may use the Table Toolbox macro and additionally wrap the Table Transformer macro in the Table Filter macro and choose a reversed filter not to show empty cells from the T1 part of the table.

[Noori Fathima]

Thanks @Katerina Kovriga {Stiltsoft} . It worked.