Confluence Table Transformer query returns 2 matches

[Noori Fathima]

Hi,

At our organization we have multiple geographical regions sharing the same JIRA board that have different Product/Market owners depending on the region. We are building a dashboard in Confluence that pulls JIRA tickets from JIRA and merges with a custom table that has Product/Market owner information. 

---

The JIRA table looks like below

Key |Project Name |Labels | Other supporting fields

Key101| A| US_Browse|----

Key201| B| UK_Cart|----

---

The custom table has below fields

Project Name | Squad name | Product Owner| Market Owner

A| Browse [US] | John | Dan

A| Browse [UK] | Leo | Sam

B| Cart [US] | Kelly | Sam

B | Cart [UK] | Liam | Sankar

The common field in both the table is 'Project Name'  and we are thinking the labels from jira table can help identify the owners for the specific region. Any help would be great :) 

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Noori Fathima,

Seems that your case may be a little bit harder than it looks. As I understand, you need to match not only projects but locations as well.

For example, you may have such custom table:

Project Name | Squad Name | Product Owner | Market Owner
A | Browse [US] | John | Dan
B | Browse [US] | John | Dan
A | Browse [UK] | Leo | Sam

So here we have two different projects, but they may be associated with the same team. And the two projects with the same name can be also associated with different teams.

So, we need to match our tables both by project names and by labels. For this purpose, the labels coming from the manually created custom table should match the labels coming from the Jira Issue macro.

Please see the screenshot below:



Note that for Confluence Cloud you should nest macros via the Table Toolbox macro.
Here is the query for the upper internal Table Transformer macro:

SELECT *,
FORMATWIKI('Project Name'+ " " + 'Labels') AS 'Project and Labels'
FROM T*



And here is the query for the lower internal Table Transformer macro:
SELECT *,
FORMATWIKI('Project Name'+ " " + 'Squad Name') AS 'Project and Labels'
FROM T*



Now we can match our tables by the newly created 'Project and Labels' column in the external Table Transformer macro:

SELECT T1.'Key', T1.'Project Name', T1.'Labels', T1.'Field 1', T1.'Field 2',
T2.'Squad Name', T2.'Product Owner', T2.'Market Owner'
FROM T1
LEFT JOIN T2 ON T1.'Project and Labels' = T2.'Project and Labels'


Hope it helps your case.