Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

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

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.



1 answer

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.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

371 views 2 11
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you