Table Filter and Charts field matching

Craig_Harley November 15, 2023

I am merging 2 confluence tables using Table Transformer and only want to display the merged list where the value of Table 1 Column A are contained within the contents of Table 2 Column B. Apologies if this is a really basic question, but have been struggling with this for a while.

Performing the Join is easy enough, just struggling to get my head around the matching

The following is as far as i have got (the original query is more complex but essentially this)

SELECT
T1.'Column A',
T2.'Column B'
FROM
T1
JOIN
T2
ON T1.'Column A' LIKE T2.'Column B'

However this returns no results

2 answers

1 accepted

3 votes
Answer accepted
Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 15, 2023

Hi @Craig_Harley , if I understand the question correctly I'm not able to reproduce this error, that means I get values returned when trying this.

I have the following setup.

image.png

image.png

Result:

image.png

Or with some added info:

image.png

image.png

 

Are you able to attach some images from your setup? It might help finding the root cause of the problem you experience.

TimK.

Craig_Harley November 15, 2023

thank for the reply Tim,

this looks identical to what I have done, the only difference being

I would be looking for something along these lines where you have A2 as a like for like match, instead I am trying to do the following

I would be looking to recognise "A2" in T1 Column A as a match for T2 Column B where the value in T2 Column B could be "entry1:A2 to documentRef1", but this would be a match

Can't really post what I have due to the nature of the content, but if needed could mock something up

Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 15, 2023

Hi @Craig_Harley, I'm not sure I fully understand when you are saying "entry1:A2 to documentRef1, , but this would be a match". Sorry about that.

Yes, I suspect a simple mockup might be of help.

TimK

Like # people like this
Craig_Harley November 15, 2023

Hi Tim, it would be a match as "entry1:A2 to documentRef1" contains A2, apologies if I have been a bit vague

In addition with the format, the field matching would always be directly after the colon ":"

mockup.PNG

Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 15, 2023

Hi @Craig_Harley, if I have understood the question correctly this example might help

SELECT 
A, C, D
FROM
T1
JOIN
T2
ON C::string->split(":")->1::string->split(" ")->0 = A

We here

  • first split the string retunrned from C by ":" and then fetch the second value from the split array which is index 1, hence "->1"
  • then we split the returned string by " " (space) and fecth the left most value which is split index 0, hence "->0"
  • we can then match the result with A in the JOIN ON clause.

Result:

image.png

Source:

image.png

Hope this helps. 

TimK

Like # people like this
Craig_Harley November 15, 2023

hi Tim, tried this, but get the following error returned

error.PNG

Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 15, 2023

Hi @Craig_Harley, strange. It works on my side. 

Can you attach a picture of your SQL statement?

TimK

Like # people like this
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 15, 2023

Hi @Craig_Harley ,

@Tim Kopperud has given a correct SQL query that also works pretty fine in our test environment. For the experiment we've used the source tables with dummy data that you provided.

Seems that indeed smth is missing in your SQL query or you try it on your real tables and they somehow differ from the provided examples.

You may share your screenshots here or refer to our support that is confidential. If you attach your page storage (upper right corner of the page -> menu ... -> View storage format), we'll be able to recreate exactly your page and macro settings and check what goes wrong.

Like # people like this
Craig_Harley November 15, 2023

as requested

Capture.PNG

Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 16, 2023

Hi @Craig_Harley, I have tried to exactly match your SQL statement, and it still works fine on my side. 

SQL

image.png

Source 

image.png

Result

image.png

 

Where are you getting the error you are refering to?  I would expect the error to be right below the SQL statement as shown below where I here have added a syntax error, but I cannot see this in your attachment. 

You might also share an image including the error to understand where it happens. 

image.png

TimK.

Craig_Harley November 16, 2023

Hi Tim, the error doesn't show up below the sql statement, but instead appears here

Capture.PNG

Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 16, 2023

Hi @Craig_Harley, I'm able to reproduse this error if I add an extra colon into the Summary column (see yellow marking below).

Error reprodused

image.png

Extra colon

image.png

I suspect the case is as @Stiltsoft support mentioned earlier, that the table data differ from the example data.

Can you have a closer look into the data set and confirm that the Summary field in each record follows the rule used, as in what you stated earlier:

...the field matching would always be directly after the colon ":"

TimK.

Like Craig_Harley likes this
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 16, 2023

Hi @Craig_Harley ,

Maybe this more general option will do for you:

Thu 9-1.png

SELECT
'Column A', 'Column B', 'Column C', 'Column D'
FROM T1
JOIN T2 ON T1.'Column C' LIKE "%" + T2.'Column A' + "%"

Thu 9-2.png

Like Tim Kopperud likes this
Craig_Harley November 16, 2023

I found a single record where there was a double colon, changed to single and the error has disappeared. I'm just getting no rows returned now, which I will work through

Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 16, 2023

Hi @Craig_Harley, glad I could be of help and that the error is gone. Just inform me here if you need further assistance with a way to return rows from the query. The formula suggested might need a little tuning to return records from your real data set. 

TimK.

Like Craig_Harley likes this
Craig_Harley November 23, 2023

Hi Tim, thanks for all the help

I had a re-think about what I was trying to achieve and found a different route using a simpler SQL statement as there were other fields I could use as the key

so this is resolved now

once again, many thanks

Like Tim Kopperud likes this
Tim Kopperud
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 24, 2023

Hi @Craig_Harley, thanks for accepting the answer. Yes, a simpler route is often a better approach. I hope you got it working as you wanted. 

TimK.

0 votes
Craig_Harley November 15, 2023

hi Tim, tried the above and I get the following 

error.PNG

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events