Table Filter and Charts String Splitting

[Craig.Harley]

I'm pulling a Jira table into the table filter and charts macro in Confluence. One of the fields returned contains a comma separated list of url links to a confluence page.

Each page has details on either an API consumer or an API provider

they are returned in the following format

Consumer URL 1, Consumer URL 2: Provider URL 1, Provider URL 2

where the consumers and provider urls are split by a ":"

Is there any way to split this into 2 columns, so that

Column 1 = everything Left of ":"

Column 2 = everything Right of ":"

Unfortunately it would have made sense to put providers and consumers into different Jira fields, but I have no control over anything done in Jira.

So is there nay way to achieve my ask?

[Aliaksei Mikhailau _Stiltsoft_ · Accepted solution]

Hello @Craig.Harley ,

It is difficult to say why the result is not as expected in your case. I noted that you used % instead of : as separator. Was it planned?

Feel free to share how your "Acceptance criteria" column in Jira is displayed to understand your case better. You can create a support ticket here.

[Craig.Harley]

as requested I have raised TFCS-6988

[Aliaksei Mikhailau _Stiltsoft_]

Hello @Craig.Harley,

As you've mentioned our Table Filter, Charts & Spreadsheets for Confluence app, let me note you can achieve the expected result using our Table Transformer macro. The example SQL query for your request is as follows:

SELECT
SPLIT_VIEW(T1.'Your column name', ":")->0 AS 'Consumers',
SPLIT_VIEW(T1.'Your column name', ":")->1 AS 'Providers'
FROM T1

[Craig.Harley]

hi Aliaksei,

thanks for this, however have tried it and it doesn't work, I get empty cells back