Table Transformer - Only display "Duplicates" Issue Link not other types of Issue Link

Katie L September 16, 2021

Hi all,

I would like to create a table with the epic keys and their Issue Links.

The tricky part is I don't want to display all the Issue Links, only the "duplicates" Issue Links

My gut is telling me there's a way where I use SQL in Table Transformer to do this.

This is the JQL I used: project = "ProjectName" and issueLinkType = duplicates 

The column Links is displaying all the Issue Links regardless of whether they're "duplicates", "relates to" or "mention in".

In my case the "duplicates" Link Issues have certain project codes (e.g. P1-, P2-, P3-). And the other types of Link Issues do not have these project codes

I'm thinking Table Transformer will be the way to do as my problem have some similarity to this question, where I think I need to write a query that splits up the Issue Link items within the one cell: https://community.atlassian.com/t5/Confluence-questions/Table-Transformer-Select-Through-Multiple-Items-Within-Key-Cell/qaq-p/1653652

Thank you so much, any help is appreciated.

1 answer

1 accepted

2 votes
Answer accepted
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.
September 16, 2021

Hi @Katie L ,

Please attach a screenshot of your Jura Issues table with the marked duplicate issues - maybe the Table Filter macro will do the trick? It is better for us to see the table before suggesting some tips.

Katie L September 16, 2021

Thanks Katerina for your quick response.

I can provide an example of the Jira Issues Table with dummy data.

Key

Linked Issues

MOCK-123

MOCK-124, P1-100, P2-110, P3-111

MOCK-123 is the Issue key

The Link Issues’ relationships to MOCK-123 are as follows:

MOCK-124: relates to

P1-100: duplicates

P2-110: duplicates

P3-111: dependent

In this case, it'd be great if only P1-100, P2-110 are displayed in the cell. Thank you so much!

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.
September 16, 2021

As the case seems to be a tricky one, I've returned with a workaround that uses two Jira Issues tables instead of the one original table - please, take a look at the screenshot below:

Thu 4-1.png

The first tables remains the same: your Epics and their linked issues.

The second table should contain only all of your issues with the duplicate status - we'll need only the "Key" column, other fields are optional.

The two tables should be wrapped into the Table Toolbox macro.

Thu 4-2.png

Inside the Table Toolbox macro your tables should be wrapped into the Table Transformer and Pivot Table macro.

Here is the custom SQL Query for the Table Transformer macro:

SELECT T1.'Key' as 'Epic keys', T2.'Key' FROM T1
LEFT JOIN T2 ON T1.'Linked Issues'->indexOf(T2.'Key') > -1

Thu 4-3.png

The last step is to create a beautiful pivot table:

Thu 4-4.png

The result table looks neat and easy-to read.

Hope this will help your case.

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.
September 16, 2021

And you may also use an additional Table Transformer macro to rename the second column.

Here I've chosen a line break as a literal value delimiter inside the Pivot Table macro settings and used the following SQL query for the second Table Transformer:

SELECT T1.'Epic keys', T1.'Literal of Key' as 'Duplicates' FROM T*

Thu 4-5.png

Thu 4-6.png

Katie L September 16, 2021

Thank you so much Katerina! That worked wonderfully and I can already think of use cases where I might use that again. 

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.
September 17, 2021

Hi @Katie L ,

I've checked the SQL query some more and noticed its unstable behavior: if the issues have different count of numbers (ex. P1-23 and P1-234) then they could be matched incorrectly (the P1-23 issue from the second table will match both P1-23 and P1-234 entries from the first table).

We'll try to come up with a better solution and I'll reach out to you, if we find something.

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.
September 17, 2021

Hi @Katie L ,

Here is the first option from our team:

Fri 5-1.png

Fri 5-2.png

SELECT T1.'Key' as 'Epic keys', T2.'Key' FROM T1
LEFT JOIN T2 ON T1.'Linked Issues'->split(", ")->indexOf(T2.'Key'::string) > -1

Fri 5-3.png

Fri 5-4.png

Fri 5-5.png

SELECT T1.'Epic keys', T1.'Literal of Key' as 'Duplicates' FROM T*

Fri 5-6.png

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.
September 17, 2021

And here is the second option from our team based on the one source table:

Fri 6-1.png

Fri 6-2.png

Fri 6-3.png

Fri 6-4.png

SELECT FIRST(T1.'Literal of Key') AS 'Key',
FIRST(T1.'Linked Issues'->split("-")->0) AS 'Type',
FORMATWIKI(SUM("[" + T1.'Linked Issues' + "|https://jsd.stiltsoft.com/browse/" + T1.'Linked Issues' + "]\\ "))
AS 'Issues' FROM T1
GROUP BY T1.'Literal of Key',
(T1.'Linked Issues')->split("-")->0

 

The https://jsd.stiltsoft.com/browse/ should be changed for your own address.

Fri 6-5.png

Fri 6-6.png

Fri 6-7.png

Fri 6-8.png

Hope that one of these options will fully suit the case.

Katie L September 22, 2021

Thank you so much Katerina for your suggestions. Here's my experience with the suggestions and what I ended up using.

 

With the first one - two table joined by pivot tables and table transformers:

  • Got rid of the incorrect partial matches (e.g. will only show P1-234, not partial match of P1-23)
  • However, if there's several Epic Key that have the same duplicates, in the end result, the duplicates will only be displayed for 1 of the Epic Keys
  • e.g. if both MOCK-123 and MOCK-456 have duplicates key of P1-234 and P1-110, in the end result, only MOCK-123 will show the duplicates of P1-234 and P1-110 and MOCK-456 will not have anything next to it.

With the second one - one source table:

  • for some reason, when I got to the pivot table step, the site keep freezing and I can't edit it anymore

 

My workaround:

Uses 1 JQL table and 1 Pivot table

  • JQL: project in ("P1", "P2" [include all product keys where the main epic have duplicates]) and issueLinkType = "is duplicated by" and status != done
    • display options: Key & Linked Issues 
    • the Key is now the duplicates (e.g. P1-, P2-) and Linked Issues are now the main project (e.g. MOCK-)
  • Pivot table: 
    • Row labels: Linked Issues
    • Calculated Column: Key
    • Operation Type: Literal
    • this produces a table with the Linked Issues (e.g. MOCK-) in the 1st column and Literal of Key (the duplicates (e.g. P1-, P2-)) in the second column
  • I then wrap the Pivot with existing tables I have using Linked Issues (e.g. MOCK-) with the SQL LEFT OUTER JOIN

This seems to be working so far with no incorrect partial matches.
Thanks again for your help!

Katie L September 30, 2021

Hi again @Katerina Kovriga {Stiltsoft} ,

Is there a solution that doesn't involve the Pivot Table macro? 

I have another problem similar to the above. I have tried to apply the same steps but the Pivot Table macro freezes and times out every time I try to use it.

I think it may be because the JQL-produced table has too many entries, it has ~200 and I need to show all of them. 

Is there a way to just use SQL in the Table Transformer macro?

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.
September 30, 2021

It seems to us that the case is not in the Pivot Table macro (and we use this specific macro for such case) - if you have 200 entries in the original table and, for example, an average number of 5 linked issues in each cell, it gives us a 1000 row table that is a really big table, especially for Confluence Cloud. Besides it is not manually created but generated by another dynamic macro.

If the Table Transformer could perform such operation (and as for now we can't come up with a simple query cause we don't know the certain number of linked issues - it always changes), we doubt that your page won't be freezing. The Table Transformer macro doesn't have any magic inside - its only difference from the Pivot Table macro is that in the first case you paste raw SQL and in the second case you use user-friendly UI.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events