Table Transformer: SQL query for display data

Question

I am struggling to display data using table transformer using multiple user IDs fields.

Created a Table Expert where I am capturing some of SME. Named the table expert on a different confluence page as SME. Below is sample data, I am trying to replicate

SME             User ID

Person A     p0s0abc

Person B.   p1s1abc

Person C.  p2s2abc

Sample SQL Query

SELECT T2.'Key',T2.'Linked Issues',
T2.'Reporter',
T1.'SME'

FROM T1
LEFT JOIN T2
ON T1.'User ID' IN T2.'Technical Lead'

This sample query works fine and is able to provide results as expected. Now, I am trying to make this a bit complex by adding another User ID Field, here is where I am facing a challenge where SQL is not recognising the query. Tried using And/OR but is giving varied results.Works fine with visualisation of data if I use the 2nd Table expert individually. But when I club them, I am not able to get the desired result.

SELECT T2.'Key',T2.'Linked Issues',
T2.'Reporter',
T1.'SME',' T1.Dev POC

FROM T1
LEFT JOIN T2
ON T1.'User ID' IN T2.'Technical Lead', 'T2.Participants'

Is there a solution to this challenge?

Stiltsoft support · Answer

Hi @Ajay Shenoy ,

There is no any difference if the tables are static or coming from the Jira Issues macros. It's just that for our examples we take static tables because we don't have all the required fields in our test Jira to match different use cases.

So, you may adjust the queries for your needs using the principles from the previous answer: use several Table Transformer macros and several User ID tables to subsequently join them and replace IDs by real names (each JOIN will replace one column).

If you need to replace one User ID in a cell, then you use the first query structure (the standard JOIN, you have this structure in your question). As I see, this is your case.

If you have a string of IDs in a cell (for example, "id1, id2, id3"), you'll need the second query from our example.

Ajay Shenoy_jgidlp · Answer

Dont think this will work for my case. As the above query is picking from Static table we have created and displaying.

1- I have a predefined JQL filter(public saved filter) which I am using to display 50+ Records and has these column (  Key, Technical Lead, Participants, Approver, Epic Name, Epic/Theme) from Jira project

2- Now I would like to display all these column for a single Jira Issue Key through a filter view in Confluence.

3- I would like to rename Technical Lead as SME , Participants as Dev POC, Approver as QE.  Currently all 3 fields show alias name SME , Dev POC, QE. These should show actual name of user and not alias name.

Along with these fields it should also display the Key and look who is the SME, Dev POC, QE, Epic name,Epic/Theme and display them in filter view in confluence. User ID fields(SME, Dev POC, QE) should show name of user and not alias name.

Sample User ID Table( Dev POC/QE- Will have name of user)

SME             User ID.      Dev POC    QE

Ajay S         a0x0abc.     a1x0abc  a2x0abc

Person B    p1s1abc.      a1x0abc  a2x0abc

Person C.   p2s2abc.    a1x0abc  a2x0abc

When we do get a Solve this is future state I am looking. Epic Name & Epic/Theme have the below sample information filled on Jira Key which should fetch and display based on JQL filter(public saved filter) I have provided for 50+ records.

Key          SME                 Dev POC             QE             Epic Name                Epic/Theme

Jira-123  Ajay Shenoy   Jennifer            Michael     Content Creation      Web

Can you suggest  a solve of this problem or work around ?

Katerina Rudkovskaya _Stiltsoft_ · Answer

Hi @Ajay Shenoy,

I can suggest the following workaround:

Here you may use the Pivot Table macro or the third external Table Transformer macro (we’ll use them later for grouping).Also I’ve taken my screenshots for Data Center, so my macros are nested directly on the page. For Cloud you’ll need to recreate the same structure inside the Table Toolbox macro. Other steps will be the same.Here is your SQL query for the internal Table Transformer macro:

SELECT T1.'Key',
T2.'SME' AS 'Technical Lead Name',
T1.'Participants'
FROM T1
LEFT JOIN T2 ON T1.'Technical Lead' = T2.'User ID'

Here is the SQL query for the second Table Transformer macro:SELECT T1.'Key', T1.'Technical Lead Name',
T2.'SME' AS 'Participants Names' FROM T1
LEFT JOIN T2 ON T1.'Participants'->indexOf(T2.'User ID') > -1

Now we need to group our issues as they were at the beginning of our manipulations.We can use the Pivot Table macro with more friendly UI:

Or the third external Table Transformer macro:SELECT T1.'Key', T1.'Technical Lead Name',
SUM(T1.'Participants Names' + " ") AS 'Participants Names'
FROM T*
GROUP BY 'Key', 'Technical Lead Name'

Hope it helps your case.