How to use Table Transformer to calculate "Static value" - "Jira macro count"

[huangbin chen]

Hi Experts,

I would like to calculate the difference between a "static value" and "Jira Macro count" as below process, but seems my script cannot achieve this.

(Note - or even "Jira Macro count" - "Jira Macro count")

May I know how to achieve this?

Expected result:

Thanks

[Stiltsoft support · Accepted solution]

Hi @huangbin chen,

The issue is not in the dynamic or static values, it's in the "issues" ending: you are trying to calculate the difference between strings (common sentences, for example, "10 issues") and numbers (for example, just "20").

So, to implement the case, you need to get rid of the "issues" endings and work only with numbers (not with strings).

SELECT *,
'B' - 'A' AS 'C'
FROM
(SELECT T1.'A' -> split(" ") -> 0 as 'A',
T1.'B' -> split(" ") -> 0 as 'B'
FROM T1)

Here we split our strings by spaces and leave only the first part (numbers) in the internal SELECT and then count the difference in the external SELECT.

[huangbin chen]

Thanks a lot, this script works well !!