Table transformer to do string operations on Jira status result

[emagnun]

In the below table, "Jira Task" column uses Jira macro to pull the info. Now how do I retrieve the status string (DONE/TO DO/IN PROGRESS) from "Jira Task" column and fill in the "Status" column? I assume I can use table transformer but I'm not sure how to do. 

Output to user now, is as below. Expectation is "Status" column should have the jira status string that I can use for further operations and keep "Jira Task" column as it is.

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @emagnun ,

You may try the following SQL query:

SELECT T1.'Product', T1.'Customer', T1.'Jira Task',
CASE
WHEN
T1.'Jira Task'->split(" ")->1 LIKE "In"
THEN
(T1.'Jira Task'->split(" ")->1 + " " + T1.'Jira Task'->split(" ")->2)
WHEN
T1.'Jira Task'->split(" ")->1 LIKE "To"
THEN
(T1.'Jira Task'->split(" ")->1 + " " + T1.'Jira Task'->split(" ")->2)
ELSE
T1.'Jira Task'->split(" ")->1
END
AS 'Status',
T1.'Note'
FROM T1

 

[emagnun]

Thanks