Table Filter and Charts field matching

[Craig.Harley]

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

[Tim Kopperud · Accepted solution]

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.

Result:

Or with some added info:

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]

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]

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

[Craig.Harley]

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 ":"

[Tim Kopperud]

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:

Source:

Hope this helps. 

TimK

[Craig.Harley]

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

[Tim Kopperud]

Hi @Craig.Harley, strange. It works on my side. 

Can you attach a picture of your SQL statement?

TimK

[Stiltsoft support]

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.

[Craig.Harley]

as requested

[Tim Kopperud]

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

SQL

Source 

Result

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.

[Craig.Harley]

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

[Tim Kopperud]

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

Extra colon

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.

[Stiltsoft support]

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' + "%"

[Craig.Harley]

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]

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.

[Craig.Harley]

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

[Tim Kopperud]

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.

[Craig.Harley]

hi Tim, tried the above and I get the following