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

emagnun December 1, 2021

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?

12 answers

2 votes
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 1, 2021

@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

2 votes
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 1, 2021

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.

1 vote
Sai velagala
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 26, 2022

Hi @Katerina Kovriga {Stiltsoft} ,  I got the solution already and I too have done the same way . But thanks for replying. Help appreciated 👍

1 vote
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 26, 2022

Hi @Sai velagala ,

The Table Transformer macro is provided by the Table Filter and Charts for Confluence app and based on the AlaSQL library (the first link leads to the macro documentation with multiple examples of queries and corresponding use cases).

The query from the stackoverflow works well for your case:

Sat 1-1.png

SELECT 'Key',
SUM('num') as 'num'
FROM (SELECT * FROM T1 UNION ALL SELECT * FROM T2)
GROUP BY 'Key'

Sat 1-2.png

And there is a more simple way to accomplish the case: wrap the Pivot Table macro on top of the Table Transformer macro (directly for Server/Data Center or via the Table Toolbox macro for Cloud).

Use the standard Merge preset in the Table Transformer macro:

Sat 1-3.png

And perform the grouping and summing via the Pivot Table macro and more comfortable UI:

Sat 1-4.png

Hope this helps your case.

1 vote
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 10, 2022

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view&edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.

1 vote
emagnun December 1, 2021

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

1 vote
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 1, 2021

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.

1 vote
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 1, 2021

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.

0 votes
Sai velagala
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 25, 2022

Hi, I have a query regarding it. I am new to SQL on the 'Table transformer' plugin but I am working on this right now and I can across a similar situation. If let's say, from the above two tables, we need to add one of the common column values' by taking another common column as reference. And the key values which are not in both the columns also should be present in the result table. Can u pls give me a query for this which will work in the table transformer plugin?

For Reference, I raised this question in stack overflow too: https://stackoverflow.com/questions/71270349/adding-values-of-column-of-2-tables-by-using-another-common-column-as-key-in-sql/71271025#71271025

0 votes
emagnun December 1, 2021

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'

0 votes
emagnun December 1, 2021

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.

0 votes
emagnun December 1, 2021

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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events