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

[emagnun]

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

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

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?

[Katerina Kovriga {Stiltsoft}]

@emagnun ,

Maybe this query will work smoothly for you:

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'

[Katerina Kovriga {Stiltsoft}]

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:

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"):

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.

[Sai velagala]

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

[Katerina Kovriga {Stiltsoft}]

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:

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

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:

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

Hope this helps your case.

[Katerina Kovriga {Stiltsoft}]

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.

[emagnun]

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

[Katerina Kovriga {Stiltsoft}]

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.

[Katerina Kovriga {Stiltsoft}]

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.

[Sai velagala]

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

[emagnun]

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'

[emagnun]

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.

[emagnun]

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. 

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

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)