You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
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)
ON T1.'Column A' LIKE T2.'Column B'
However this returns no results
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.
Are you able to attach some images from your setup? It might help finding the root cause of the problem you experience.
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
Hi @Craig.Harley, if I have understood the question correctly this example might help
A, C, D
ON C::string->split(":")->1::string->split(" ")->0 = A
Hope this helps.
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.
Hi @Craig.Harley, I have tried to exactly match your SQL statement, and it still works fine on my side.
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.
Hi @Craig.Harley, I'm able to reproduse this error if I add an extra colon into the Summary column (see yellow marking below).
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 ":"
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.