Merging and removing dropdown values from Jira with Table Transformer

[Aurélien Thazet]

Hi everyone, 

I try to get some values from Jira and consolidated them in a Table Transformer into Confluence. The table I get is look like this right now:

And here is what I want to have as a final table:

In a nutshell:

• I want to create one line per Jira Version and group Summary and Topic related on this line
• For the Topic column, I want to:
  • check all the Topics from my Jira issues to remove duplicates if they are some for the same Version line
  • Change their style by using Confluence status style

I already managed the first one thanks to the following SQL request:

SELECT T1.'Fix versions' AS 'Version',
CONCAT_VIEW_AGGR(FORMATWIKI('Summary' , " \n")) AS 'Summary',
CONCAT_VIEW_AGGR(FORMATWIKI('Topic' , " \n")) AS 'Topic'
FROM T*
GROUP BY T1.'Fix versions';

But I'm struggle with the Topic column. Any advice?

[Stiltsoft support · Accepted solution]

Hi @Aurélien Thazet ,

Your SQL query is correct. If in the 'Topic' column coming from your Jira Issues macro you have statuses, then they will be grouped as statuses (with color lozenges). If you have comma-separated strings (plain text), then you'll receive grouped plain text.

With the help of the Table Transformer macro you are able to set statuses based on some condition. If your 'Topic' column had only one word per cell, we could check which word it is and set a correspondent status instead. But, as I see from your screenshots, there can be several different words per cell. So, this workaround won't do for the case.

[Aurélien Thazet]

Hello, 

Thanks for your swift reply. I could have several statuses in one cell indeed. Do you have any workaround for this?

[Stiltsoft support]

Not sure that the workaround is worth the end goal but you may try the following structure:

The same Jira output is repeated twice.

#1

SELECT'Col 1',
CONCAT_VIEW_AGGR(FORMATWIKI('Col 2' , " \n")) AS 'Col 2'
FROM T*
GROUP BY 'Col 1'

#2

SEARCH / AS @a EX('Col 3'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', @a->'Col 2' AS 'Col 2', _ AS 'Col 3') FROM T*

#3

SELECT *,
CASE
WHEN 'Col 3' LIKE "Done" THEN FORMATWIKI("{status:colour=Green|title=Done}")
WHEN 'Col 3' LIKE "New" THEN FORMATWIKI("{status:colour=Yellow|title=New}")
ELSE FORMATWIKI("{status:colour=Red|title=Fail}")
END AS 'Col 3'
FROM T*

#4

SELECT'Col 1',
CONCAT_VIEW_AGGR(FORMATWIKI('Col 3' , " \n")) AS 'Col 3'
FROM T*
GROUP BY 'Col 1'

#5

Here we choose the standard Lookup preset by the 'Col 1' column.

[Aurélien Thazet]

Thanks for the proposition. I tried to reproduced it and everything went fine until step 5. The tables aren't merging.

I tried to OUTER JOIN them with the following SQL request:

SELECT T1.'Col 1',
T1.'Col 2',
T2.'Col 3'
FROM T*
OUTER JOIN T* ON T1.'Col 1' = T*.'Col 1'

But I got the below error:

TypeError: Cannot read properties of undefined (reading 'Col 1')

[Stiltsoft support]

You don't need any requests for this, just choose the standard Lookup preset and the suitable column for merging (as it is only one, it will be set automatically):

[Aurélien Thazet]

Thanks for your help. I think we are close of the final shape I'm looking for.

The only left is the status duplicates in Col 3. I would like to remove the duplicates in the same cell. Is it possible?

[Stiltsoft support]

You may add an additional Table Transformer macro between #2 and #3 with the following SQL query:

SELECT 'Col 1', 'Col 3'
FROM
(SELECT
DISTINCT('Full String'),
'Col 1', 'Col 3'
FROM
(SELECT 'Col 1' + 'Col 3' AS 'Full String',
'Col 1', 'Col 3'
FROM T*))

Please note that on the real data set there may be low performance. Two Jira macros and six Table Transformer macros are more than enough, especially for Confluence Cloud. 

[Aurélien Thazet]

Thank you for your help. This fixes my problem.

As you mentioned it could be low performance for Confluence Cloud.