Table Transformer with Jira Status merged

Aurélien Thazet

Hello everyone, 

 

I'm using Jira Issues to retrieve some issues of a Jira Project and create a schedule in Confluence thanks to Table Transformer. Here is the current display I have:

 

Here is my current SQL request:

SELECT T1.'Fix Versions' AS 'Version',
CONCAT_VIEW_AGGR(FORMATWIKI('Summary' , " \n")) AS 'Milestone',
CONCAT_VIEW_AGGR(FORMATWIKI('Start date' , " \n")) AS 'Start Date',
CONCAT_VIEW_AGGR(FORMATWIKI('Due' , " \n")) AS 'Due Date',
CONCAT_VIEW_AGGR(FORMATWIKI('Status')) AS 'Status'
FROM T*
GROUP BY T1.'Fix Versions'

 

I would like to display only one value per Version line in the Status column. For example with the first version of the table: I want to display a status message named "COMPLETED" in the status cell because each issue status for this version is Done, Failed or Not applicable.

 

Any advice on how I can do this can of things ?

Stiltsoft support

Hi @Aurélien Thazet ,

You may try to insert another Table Transformer macro on top and use the CASE WHEN statement: https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-UsingIf/Thenconstruct

For example, if you have "To do", "In progress", "Done", "Failed" or "Not applicable" statuses, you may start with the checking if the cell contains the "To do" status. If yes, then you put the "Needs work" status.

Then you check for the "In progress" status and set the "Current work" status.

Other statuses that mean "Completed" go to the ELSE part.

SELECT *,
CASE WHEN 'Status' LIKE "%To do%"
THEN "Needs work"
WHEN 'Status' LIKE "%In progress%"
THEN "Current work"
ELSE "Done"
END
AS 'Overall Status'
FROM T1

Hope it helps your case.