Using Table Transformer with Partial Text Match Joins

Question

Hi, I'm trying to use Table Transformer to link two tables from different systems... One table is from JIRA. The other is a service ticket system - each service ticket may have one or more JIRAs linked to it - these links are not visible in JIRA. From JIRA I've got the JIRA ID, like 'PROJ-12345' (T1.'Key') From the service ticket system I've got one field with a list '##PROJ-123456##PROJ-65432##PROJ-98465' (where # is a non-visible character) (T2.'JIRA') So I want to write something like... SELECT * FROM T2 LEFT JOIN T1 ON T2.'JIRA' contains T1.'Key' I tried using this examplar as a model: https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Mergingtablesbypartialmatch 'Out of the box' it only makes a link on the last JIRA in the T2.'JIRA' string (probably the extra characters getting in the way?) So I tried wrapping T2 in a table transformer so I could 'pre-process' out the non visible characters. The easiest way I found was to use: MATCH_REGEXP(T1.'JIRA',"[0-9]{1,6}","g") -- T2 is T1 in this context :) This gives me a comma separated list, albeit without the 'PROJ-' bit. But no matter I amended the examplar: SELECT * FROM T2 LEFT JOIN T1 ON T2.'JIRA'->split(",")->indexOf(REPLACE(T1.'Key', "PROJ-", ""))>-1 But I get an error: TypeError: (y[0] || {}).split is not a function Presumably something to do with my 'pre-processing'? Any one found a way to do this?

Stiltsoft support · Answer

Glad it works! And maybe your ticket will get to another team member, so you may even get another working solution as the same task sometimes can be achieved in many ways.

Also if you have any specific questions regarding the app in the future, it is better to create support requests. Besides the confidentiality of the official portal, we see all the created tickets immediately. Here, in the Community, we also try to answer all the questions, but we find them manually (by tags, by macro names, and so on). Maybe some of the questions are missed.

Niall Poole_aj4t7x · Answer

This works!

I've still got the MATCH_REGEX in a nested table transformer at the moment, but I'll try it all in one to see if that works too as it would be a bit more elegant.

I've not got any real experience with JS and regex so didn't realise I could simplify it like this. I think this means you can probably simplify the exemplar on you website too :)

I did already raise a support ticket, but I'll add a comment to it.

Thanks, I'm going to build some really useful pages now!

Niall.

Stiltsoft support · Answer

Also I've tried to recreate your case by the description and got smth like this:

In the internal Table Transformer macro we get rid of the ##:

SELECT *,
MATCH_REGEXP(T1.'JIRA',"PROJ-[0-9]{1,6}","g") AS 'JIRA' FROM T*

In the external Table Transformer macro we join our tables:

SELECT T2.'Key', T2.'Data',
T1.'JIRA', T1.'Notes'
FROM T1
LEFT JOIN T2 ON T1.'JIRA'->indexOf(T2.'Key') > -1

Also the step with the internal macro and MATCH_REGEXP seem to be an extra step. Of course, I don't know how it looks for you exactly when coming from Jira, but for the strings like "PROJ-12345PROJ-65432PROJ-98465" (without any separating symbols) the external Table Transformer macro with its query seems to be enough.

Maybe it gives you some hints for the case. And you may still refer to our support portal for further discussion.