Table transformer complex field access from Jira

[Smadar Sagi]

I'm trying to write QCL transformer for data coming from Jira. one of the Jira fields we are getting is a complex field(table with 3 columns)

is there ability to get information from the second or 3 column of that field

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Smadar Sagi , @Hagai Danenberg ,

I've consulted with our developers and was provided with a following example - hope it may come in handy for your cases:

SELECT
'Second td'->split("</")->0->split(">")->1
AS 'Second',
SUBSTRING('Second td', INSTR('Second td', "</td>") + 5)->split("</")->0->split(">")->1
AS 'Third'
FROM (
SELECT *,
SUBSTRING(T1.'Project'->tfView, INSTR(T1.'Project'->tfView, "</td>") + 5) AS 'Second td'
FROM T1)

[Smadar Sagi]

we have tried it and it's working. we are still experiencing some performance issues with the query could this cause that?

[Katerina Kovriga _Stiltsoft_]

Hi @Smadar Sagi

As usual our macros don't impact the page loading time - try to remove the macros and leave only the Jira Issues macro to compare.

If the problem persists, you may raise a support ticket: go to the upper right corner -> menu ... -> View Storage Format and attach it to the ticket (we'll recreate your macros and SQL queries).

The Jira Issues macro won't be recreated, so please attach the screenshot of the Jira Issues table. Maybe we'll come up with a better solution.

[Hagai Danenberg]

I have the same Issue, for now I am using substring, but it slows down the query tremendously. 
Also If column 1 is not fixed length it is hard to know when to start the substring..... column 3 in my case with this solution is not usable. 

Looking too for any help from anyone.