join table with table transformer based on parent elements

[boellner boellner]

Hello,

I got 2 tables:

Table 1:
T | Key | Summary | Epic Link | Parent Link 

T can be a Epic. Then it has a Epic Link
Or T can be a Story. Then it has a Parent Link.

Table 2:
T | Key | Summary

With table-transformer I want to get a table with all elements of table 1 plus if it has a parent link then the extra fields of T2, or if it has a Epic Link then the extra fields of T2.

My sample with only Epic Links is:

SELECT T1.'Key', T2.'Key' as 'Parent Key'
FROM T1 INNER JOIN T2 ON T1.'Epic Link' = T2.'Key'

But it gives me no entries.

Thank you

[Aliaksei Mikhailau {Stiltsoft}]

Hello @boellner boellner,

As you've mentioned the Table Transformer macro, it seems that you use the app that we develop - Table Filter, Charts & Spreadsheets for Confluence.

Could you please try the following SQL query and let me know if it works fine?

SELECT T1.'Key', T2.'Key' as 'Parent Key'
FROM T1 INNER JOIN T2 ON TEXT(T1.'Epic Link') = TEXT(T2.'Key')

[boellner boellner]

If I enter like this, I get:
TypeError: Cannot read properties of null (reading 'getView')

 
Maybe the problem is here:
Epic Link have a prosed text like: [XYZ] DESCR OF THE EPIC
The Key is more in this form: PROJECT-1234
So I dont know how both match or what other link element I need to use.

[Aliaksei Mikhailau {Stiltsoft}]

Is XYZ (as in your example) in T1.'Epic Link' is the same as the T2.'Key' (e.g. PROJECT-1234)? Or maybe T1.'Epic Link' is the same as T2.'Summary'?

[boellner boellner]

yes summary is epic link, thank you

[Aliaksei Mikhailau {Stiltsoft}]

Hello @boellner boellner ,

If summary is epic link, please try the following SQL query:

SELECT T1.'Key', T2.'Key' as 'Parent Key'
FROM T1 INNER JOIN T2 ON T1.'Epic Link' = T2.'Summary'

[boellner boellner]

I have problem with 

T2.'Summary'

if it has special characters. How can this be omitted?

[Aliaksei Mikhailau {Stiltsoft}]

Hello @boellner boellner ,

Could you please provide as many details as possible to this problem so I can check possible solutions? 

[boellner boellner]

T3 is a filter table for Epics
T4 is a filter table for Stories

select * from 
...

LEFT JOIN T4 ON T3.'Epic Name' = T4.'Epic Link'

 I’m using the Epic name and not summary, because if summary is changed that the query will not find it any more. If Epic name is changed, that the query will find it.

In this example epic summary and epic name is both für Driver...
In the query it is shown as 

Epic Summary:
für Driver...

Epic Name:
für Driver...

The story epic Link is
für Driver...

[Aliaksei Mikhailau {Stiltsoft}]

Hello @boellner boellner ,

Thank you for your explanation.

Based on what I found, there are issues with Epic Name field encoding of special characters in Jira macro, there are some links for the corresponding issues you can vote for:

https://jira.atlassian.com/browse/CONFSERVER-54431

https://jira.atlassian.com/browse/CONFSERVER-58379

https://jira.atlassian.com/browse/CONFSERVER-60082

[Katerina Rudkovskaya _Stiltsoft_]

Hi @boellner boellner,

Please check a workaround that may work for you in this thread.