Table Transformer macro - complex query with splitting and joining tables

[Emil]

Hi,

I would like create a bit tricky query from 2 tables with the Table Transformer macro.

The Teams table contains the Name field where names and PersonIDs are stored together. There are two tricky things here:

• more names can be stored in the Name field,
• brackets can be anywhere in the Name field, not only before and after the PersonID - so that's why I was wondering how to split up the field.

My planned steps:

1) First I would need some support to split up the Name field to get only the PersonIDs (e.g. PersonID-12345, PersonID-15635) from that.

2) And then I would like to join the PersonIDs with LEFT JOIN with the Persons table to have the Person's nickname values.

3) And finally I would like to have the Persons nickname field in the original Team table with the nicknames.

See the sample tables:

Teams table
Team	Name
TeamID-1	John Doe (alias: Johnny) (PersonID-12345) Susie Wilson (PersonID-15635)
Persons table
PersonID	Person's nickname
PersonID-12345	John
PersonID-15635	Susie
Desired Teams table after table transformation
Team	Persons nickname
TeamID-1	John, Susie

Thanks a lot if you can support with this.

Best regards,

Emil

[Stiltsoft support · Accepted solution]

Hi @Emil ,

You can try the following workaround with the MATCH_REGEXP function:

The internal Table Transformer macro:

SELECT T1.'Team',
MATCH_REGEXP(T1.'Name', "PersonID-\d+", "g") AS 'PersonID'
FROM T1

The external Table Transformer macro:

SELECT T1.'Team',
FORMATWIKI(SUM(T2.'Person\'s nickname' + ", ")) AS 'Person\'s nickname'
FROM T1 LEFT JOIN T2 ON T1.'PersonID'->split(",")->indexOf(T2.'PersonID'::string) > -1
GROUP BY T1.'Team'

Hope it helps your case.

Besides, if you have questions regarding the Table Filter, Charts & Spreadsheets for Confluence app (this is our add-on and the Table Transformer macro also belongs to it), it is better to refer to our support portal that is confidential.

There you'll be able to share your page storage format with us to recreate your source tables and your macros. Besides, we also won't miss your questions (in the Community we just manually browse all the posts and try to find relevant questions if there are any).

[Emil]

Hi @Stiltsoft support ,

Thanks for the quick response. I guess we are almost there. I have tried it and the only question is how to display the other fields of the Teams table (because there are other fields in that table, although I highlighted only the important ones in the example). Please see the Location field in the Team table as an example.

When I use this query with SELECT *, in the internal Table Transformer everything is fine and the Location field is also displayed in the table.

SELECT *,
MATCH_REGEXP(T1.'Name', "PersonID-\d+", "g") AS 'PersonID'
FROM T1

But when I am trying to use this query with SELECT *, in the external Table Transformer I get the result in the last image and the Location field doesn't show up. 

SELECT *,
FORMATWIKI(SUM(T2.'Person\'s nickname' + ", ")) AS 'Person\'s nickname'
FROM T1 LEFT JOIN T2 ON T1.'PersonID'->split(",")->indexOf(T2.'PersonID'::string) > -1
GROUP BY T1.'Team'

Should the grouping of the rows happen in a different way?

P.S.: Thanks for the sharing your support portal page URL as well. Next time I will open a topic there. Now I didn't want to open a new thread.

Thanks.

Emil 

[Stiltsoft support]

Hi @Emil ,

You may use the following query for your external Table Transformer macro:

SELECT T1.'Name', T1.'Next Column',
FORMATWIKI(SUM(T2.'Person\'s nickname' + ", ")) AS 'Person\'s nickname'
FROM T1 LEFT JOIN T2 ON T1.'PersonID'->split(",")->indexOf(T2.'PersonID'::string) > -1
GROUP BY T1.'Team', T1.'Next Column'

[Emil]

Hi @Stiltsoft support ,

It worked very well. Thanks a lot!