How to list all tasks for an epic using Table Transformer

[Angela Bultemeier]

Hello! I am using a Table Transformer macro to join 2 tables. For simplicity, T1 has 1 Epic and T2 has 1 Task. The Epic Link for the Task in T2 is the Epic in T1.

I'd like to display the Epic in column 1 and the Task in column 2 (ideally, all tasks in the Epic, but I am starting with just one for simplicity).

I can easily do this with Initiatives/Epics when T1 has my Initiatives query and T2 has my Epics query using this SQL:

SELECT T1.'Key' AS Initiative, T2.'Key' AS EPIC
FROM T1 LEFT JOIN T2 ON (T1.'Key' = T2.'Parent Link')

However, when I try similar logic for the Epic/Task, it doesn't work. Here are some SQL snippets I have tried, none of which work:

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T2.'Key' IN T1.'Issues in Epic')
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Issues in Epics'->split(" , ")->indexOf(T2.'Key'::string) > -1)
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Child in Epics'->split(" , ")->indexOf(T2.'Key'::string) > -1)
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Key' = T2.'Epic Link')
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Epic Name' = T2.'Epic Link')
GROUP BY T1.'Key', T2.'Key'

None of these queries work. Any advice?

Result of the Initiatives/Epic join (perfecto!):

Result of the Epic/Task join:

[Angela Bultemeier · Accepted solution]

@Katerina Kovriga _Stiltsoft_ - you seem to be an expert. I'd love any advice you might have. :) 

[Stiltsoft support]

Hi @Angela Bultemeier ,

Please provide us with two screenshots of the tables that you are trying to merge: with visible headers and 1-2 rows of data for an example. If your data is sensitive, you may recreate these tables manually using dummy data but preserving the initial cell structure. We'll try to provide you with a suitable query.

[Angela Bultemeier]

Ah - your question solved my dilemma. I had incorrectly assumed  that T1 had Epic objects and T2 had issue objects, but I had failed to consider the columns defined by the display options of the Jira/Issue Filter I was using to create T1 and T2. Including Epic Name and Epic Link in the display options as columns for T1 and T2 obviously resolved my quandary. Thank you!

[Angela Bultemeier]

@Stiltsoft support  Hello! I thought I had solved this yesterday, but I must have mis-poken.

Table 1 is a Jira/Issue Filter that returns one epic:

Table 2 is a Jira/Issue Filter that returns one task:

Sample output with the SQL Query "SELECT * FROM T*":

Sample output with the SQL Query:

SELECT T1.'Key' AS Epic, T2.'Key' AS Task
FROM T1
LEFT JOIN T2 ON (T1.'Key' = T2.'Epic Link')
GROUP BY T1.'Key', T2.'Key'

I have almost the exact same Table Transformer code working with Initiatives and Epics, but can't figure out why this isn't working. :( Any help appreciated.

For reference, here is my Initiative/Epic SQL Query that works:

SELECT T1.'Key' AS Initiative, T2.'Key' AS Epic
FROM T1
LEFT JOIN T2 ON (T1.'Key' = T2.'Parent Link')
GROUP BY T1.'Key', T2.'Key'

And the resulting output that works:

[Stiltsoft support]

Please give us the two screenshots of your tables that you are trying to merge (without any macros).

From the screenshots of your Jira Issues we can see that you have, for example, epic links in your tables - maybe they will be suitable for merging.

[Angela Bultemeier]

@Stiltsoft support - I'm confused. I provided screenshots of the tables above. ^^ They are Jira/Issue Filter tables.

T1 uses the filter "issuekey in (offers-6281)" and contains 1 Epic. The columns are Key, Summary, IssueType, Status, Resolution, Epic Name, Epic Link.

T2 uses the filter "labels in (AlphaLiveData) and contains 1 Task. The columns are Key, Summary, IssueType, Created, Priority, Status, Epic Link, and Epic Name.

What am I misunderstanding?

[Stiltsoft support]

Hi @Angela Bultemeier ,

We've meant the screenshots of your full separate tables - I understood later that the third screenshot was a combination of your separate tables. The first row belongs to the first table and the second row - to the second table.

So, here is how I've got your case:

SELECT T2.'Key' AS 'Epic',
FORMATWIKI(SUM(T1.'Key' + " \\ ")) AS 'Task'
FROM T1 LEFT JOIN T* ON T1.'Epic Name' = T2.'Epic Name'
GROUP BY T2.'Key'

As you may notice, when we concatenate rows, the links are missing. So if you need links for your tasks, you may use the following workaround with the Pivot Table macro:

SELECT T2.'Key' AS 'Epic',
T1.'Key' AS 'Task'
FROM T1 LEFT JOIN T2 ON T1.'Epic Name' = T2.'Epic Name'

Now your links are present in both columns. And you may use additional Table Transformer macro to rename the second column in a prettier way if required.

Hope that this helps or at least gives you some ideas for the case.

[Angela Bultemeier]

Thanks, @Stiltsoft support . I got it to work! Thank you! Some context since the solution is a bit different than what was prescribed.

As a side note, no need to use a Pivot table to get links. This is the code we use:

FORMATWIKI(CONCAT("[", T1.'Key', " ", ESCAPEMARKUP(T1.'Summary'), "|https://jiraURL/browse/", T1.'Key', "]\\ ")) AS Epic

I understand what you mean by the table screenshots now. Both are Jira/Issue Filters.

T1 Definition

JQL: issuekey in (offers-6281) 

Screenshot of table output:

T2 Definition

JQL: labels in (AlphaLiveData) 

Screenshot of table output:

Output Based on Suggested SQL Query

SQL Query (adjusted since T1 has the Epic and T2 has the Task): 

SELECT T1.'Key' AS 'Epic',
FORMATWIKI(SUM(T2.'Key' + " \\ ")) AS 'Task'
FROM T1 LEFT JOIN T* ON T2.'Epic Name' = T1.'Epic Name'
GROUP BY T1.'Key'

Screenshot of output:

Conclusion

Note that your question prompted me to output everything from the Jira Issue/Filters table to really analyze the data (thank you!) and I noticed an ampersand in the Epic Name. Removing the ampersand from the Epic Name resolved my woes. Wa-la!

[Stiltsoft support]

Thank you for reaching out! Happy that everything is working now as expected.

If you have any other questions and your data is sensitive, note that you may always create a support request. Our portal is based on Jira Service Desk and it's confidential (so you may share your screenshots freely).

[Angela Bultemeier]

Thank you! I'm sharing final data on the Table Transformer for reference below, including code on how we're making each Task a link based on the Task Summary. Tables are as noted in my last comment.

Table Transformer SQL Query:

SELECT

FORMATWIKI(

   CONCAT("[", T1.'Key', " ", ESCAPEMARKUP(T1.'Summary'),

 "|https://jiraURL/browse/", T1.'Key', "]\\ ")) AS Initiative,

FORMATWIKI(SUM(

  CONCAT("[", T2.'Key', " ", ESCAPEMARKUP(T2.'Summary'), "|https://jiraURL/browse/", T2.'Key', "]\\ "))) AS 'Task'

FROM T1

LEFT OUTER JOIN T2 ON T1.'Epic Name' = T1.'Epic Name'

GROUP BY

FORMATWIKI(

   CONCAT("[", T1.'Key', " ", ESCAPEMARKUP(T1.'Summary'),

 "|https://jiraURL/browse/", T1.'Key', "]\\ ") ),

T1.'Labels',

T1.'Target Start',

T1.'Target End'

Output: