Table transformation how to check for sub tasks and ignore

Nags Subramanian
Contributor
May 15, 2024

hi,

 

below is my jql filter i am using

project = ISOCQAT3 AND component = Deploy AND "End date" >= 2024-01-01 AND "End date" < 2025-01-01 ORDER BY "End date" ASC

 

and below is the table transformation query

 

select T1.'T' as 'Issue Type'

, T1.'Start date', T1.'End date' from T1

and the output is something like

Capture.PNG

Fist line is a sub task of the second line which is a story.

i should exclude stories if it has sub tasks and dont display, and display only the first line in this case.if the story does not have any sub-tasks i should display that story.

 

how do i do that please? can something be done for this in the selection query itself or can it be done in the table transformer code?

 

thanks

Nagarajan.

 

2 answers

0 votes
Holly Esparza
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
November 4, 2024

If you have Scriptrunner, it would be easy to do this in a Jira JQL filter rather than using Table Transformer: 

issuefunction not in hasSubtasks() or issuetype = sub-task

0 votes
Stiltsoft support
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.
May 16, 2024

Hi @Nags Subramanian ,

With the help of the Table Transformer or Table Filter macro you'll be able to filter issues of a specific type. What concerns your case that looks rather complicated, we can suggest looking into this part of our documentation:

https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-MergingJiraIssuetablesbylinkedissuesandkeys

If you can create two Jira tables (one for stories and one for the linked sub-tasks), then you'll be able to join these tables and have a kind of an overview of your stories and their sub-tasks if there are any.

Nags Subramanian
Contributor
May 16, 2024

tricky, if i apply any sort of filter on the transformed table, my serial number added at the beginning of the table using rownum() becomes disarray since some of the rows get filtered out.

is there something i can use in the selection query itself , where in select only records which does not have sub-tasks?

Stiltsoft support
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.
May 16, 2024

What concerns the macro organization in general, you may combine your macros in different ways.

For example, you may filter your table with the help of the Table Filter macro and then put the Table Transformer macro on top of it to apply the ROWNUM() function and rename your columns. Then the ROWNUM() function will be applied to the filtered table and not to the original one.

Also, regarding your initial case: if you need to just select rows that don't have sub-tasks, then you can just apply a filter on the Sub-Tasks field.

From the screenshot from your original question I can see that you have stories and sub-tasks in one table. So, for stories the Sub-Tasks field can be empty or not empty and for tasks it is always empty.

So, you may use the Table Filter macro and filter empty cells of the Sub-Tasks field. Or your may filter with the help of the Table Transformer macro using the WHERE clause:

SELECT * FROM T1

WHERE 'Sub-Tasks' IS NULL

Nags Subramanian
Contributor
May 16, 2024

thanks. 'Sub-tasks' is NULL check on the table transformer query worked perfectly fine.

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events