Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Table transformer to add a column only if it doesn't exist in source table

Below are the two tables "actual" and "planned" for the Jira tasks. 

Capture.JPG

The "actual" table is the result of the Jira query based on the current Jira status as below.

Capture1.JPG

Ideally, when people start working on tasks, there will be more columns like "Count In Progress" and "Count Done". But currently these columns are not there in the output, as you can see.

I want to merge these two tables "actual" and "planned" based on sprint name and then add columns  "Count In Progress" and "Count Done" only if they don't exist. How do i use table transformer for this?

1 comment

Hi @emagnun ,

You may try to wrap both of your tables (or Table Excerpts) subsequently into the Table Transformer and then into the Table Filter macro.

Then you look up your tables with the help of the Table Transformer macro:

Wed 3-1.png

SELECT T1.'Sprint / Status' AS 'Sprint name', *
FROM T1 LEFT JOIN T2 ON T1.'Sprint / Status' = T2.'Sprint'

The Table Filter macro helps you to hide the duplicate unwanted columns (for example, "Sprint/Status" and "Sprint"):

Wed 3-2.png

As we used * in the Table Transformer query, there shouldn't be any errors if you have or don't have certain statuses in the result Pivot table obtained from the Jira Issues macro.

Like # people like this

Sorry this doesnt solve my real problem -- I need "Count In Progress" and "Count Done" to be added to my final table, if the Jira query used in "actual" table doesn't return those columns. In my current scenario Jira query only returns "Count To Do" as you can see in my "actual" table.

 

Below is what I tried. 

Capture.JPG

Inner table transformer combines both table based on sprint. (Result is good)

Capture1.JPG

Capture2.JPG

 

Outer table transformer checks and add columns "Count In Progress" and "Count Done". (My SQL seems wrong here .. doesnt work.. pls help me on this)

Capture2.JPG

Then I don't get your use case - what do you suppose to keep in these two columns? And what is supposed to happen when the columns with the same names appear in the table via the Jira Issues macro?

As I understand from your SQL query, you want to somehow create an extra column (or columns according to the set of your statuses) that will be present constantly, but if there is no such column obtained from the Jira Issues macro then you want to pull the column with the same name from the second manually created table full of NULLS in every cell?

I'm not sure of the possible SQL query, never came across the similar case, but you'll get a full table once you'll get the first sprint in the next status.

Let me simplify the use case:

Source table gets dynamically created based on Jira query, but the number of columns will not be same always. (Lets say Maximum number of columns in source table is 3, but currently it has 1 column only)

Now, I want to pass this source table to a table-transformer to figure out which column is non-existing and then add that explicitily with all 0 value.

Finally I got a workaround to add non-existing columns... Not sure if this is the best way but at least it worked.

CASE
WHEN LENGTH(T1.'Count In Progress') NOT NULL THEN T1.'Count In Progress'
ELSE ''
END
AS 'Count In Progress'

Does it work when the first table returns the "In progress" column? 

For me the query pastes an empty column but the result is somehow messed up when the first column contains the related column.

You're right. it fails if column does really exist. 

I modified the query as below and looks like this works for both existing/non-existing scenarios. Pls share your thoughts on this.

CASE
WHEN T1.'Count In Progress' LIKE NULL THEN 'nil'
ELSE T1.'Count In Progress'
END

@emagnun ,

Maybe this query will work smoothly for you:

Thu 5-1.png

SELECT T1.'Sprint / Status' AS 'Sprint',
COALESCE(T1.'Count TO DO', T2.'TO DO') AS 'TO DO',
COALESCE(T1.'Count IN PROGRESS', T2.'IN PROGRESS') AS 'IN PROGRESS',
COALESCE(T1.'Count DONE', T2.'DONE') AS 'DONE'
FROM T1 LEFT JOIN T2 ON T1.'Sprint / Status' = T2.'Sprint'

Thu 5-2.png

Comment

Log in or Sign up to comment
TAGS
Community showcase
Published in Confluence Cloud

🏠 Say hello to the new Confluence Home!

Hi Atlassian Community, My name is DJ Chung, and I’m a Product Manager on the Confluence Cloud team. Today, I’m excited to share a new and improved version of Home. The new Home helps you ...

968 views 7 42
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