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
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.
TimK.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ":"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hope this helps.
TimK
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi Tim, tried this, but get the following error returned
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Craig.Harley, strange. It works on my side.
Can you attach a picture of your SQL statement?
TimK
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
TimK.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Tim, the error doesn't show up below the sql statement, but instead appears here
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ":"
TimK.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Craig.Harley ,
Maybe this more general option will do for you:
SELECT
'Column A', 'Column B', 'Column C', 'Column D'
FROM T1
JOIN T2 ON T1.'Column C' LIKE "%" + T2.'Column A' + "%"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi Tim, tried the above and I get the following
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.