How can I parse a column with a list of comma separated values and create new rows for each

Adam Petrie September 24, 2024

I am presently using Table Transformer on Jira data and getting a Linked Issues for a list of Epics of interest. Looks like this sample.

When there is more than 1 linked issue I'd like to create a row in my table for each of the linked issues and duplicate all the other columns.

Help appreciated.

image.png

2 answers

1 accepted

4 votes
Answer accepted
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.
September 24, 2024

Hi @Adam Petrie ,

If you are talking about the Table Transformer macro provided by the Table Filter, Charts & Spreadsheets for Confluence app, you may check this Community thread. Seems that it's exactly what you are trying to do.

Adam Petrie September 24, 2024

I'm looking at the referenced thread. It does appear to be exactly what I'm trying to solve. When I try to implement I'm getting an error I do not understand.

image.pngimage.png

Adam Petrie September 24, 2024

underlying table I'm trying to run above query on

image.png

Adam Petrie September 24, 2024

The values come from Jira are coming has HTML links. Is there another step to be taken so they are only text?

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.
September 24, 2024

Please follow the given example: remove the T1. parts from your query when you refer to columns. Your error means that you are trying to transform, for example, your 'Key' column from the original T1 table and then rename it AS T1.'Key' that means the same column from the original table (and we don't have the original table after the transformation anymore). Obviously it is impossible, hence the error.

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

 

Like Adam Petrie likes this
Adam Petrie September 24, 2024

Thanks very much for the prompt responses and tutoring. Clearly I'm new to this and still learning. :)

Like Stiltsoft support likes this
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.
October 31, 2024

Hi @Adam Petrie ,

Here we are with the updates regarding the app: today we've introduced the SPLIT_VIEW function for the Table Transformer macro (for Data Center it will be 12.1.2 version, for Cloud you'll get the updates automatically).

Now when using the following query

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

the separated Linked Issues won't lose their formatting and will stay as links.

Thu 5-1.png

Hope it helps your future cases.

Like Adam Petrie likes this
0 votes
Jim Knepley - ReleaseTEAM
Atlassian Partner
September 24, 2024

I think the SQL-based transformers use the H2 database internally, and splitting a string in H2 is a little involved.

If you are using the SQL-based table transformer, could you provide the SQL query that your table is based on? Hopefully, we can unwrap what's building Linked Issues at the source, instead of trying to manipulate the string contents of those cells.

Jim Knepley - ReleaseTEAM
Atlassian Partner
September 24, 2024

(ignore this, the answer that @Stiltsoft support posted is better)

Like Adam Petrie likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events