Table toolbox Macro pattern match

[Maria Del Rosario Gonzalez]

I've been using the table toolbox macro with great success and have encountered an error/edge case I am not able to work through.

I have a label field that I am pulling from Jira that I successfully translate into  an corresponding estimate equivalent in hours based on a specific pattern.  I use a case statement with:

Match_Regexp(Table.field, "Pattern1") then 50

Match_Regexp(Table.field,"Pattern2") then 100

etc. as 'Estimate'

I would like to check an error/edge case were someone has incorrectly entered both Pattern1 and Pattern2 in the field and have an error message displayed in the 'Estimate' column.

I have attempted to use

count (match_regexp (Table.field, "Pattern*"))

but that tells me the number of rows with the pattern.  I need the number of times the patterns appears in Table.field.

Thank you kindly for any assistance!

[Stiltsoft support · Accepted solution]

Hi @Maria Del Rosario Gonzalez ,

If I've understood you correctly, you have a bunch of statuses (labels) that your users type in manually and you work with them in Confluence using the legacy Jira Issues macro. For simplicity let's pretend that we have two valid labels: Pattern1 and Pattern2. But your users may type in smth different, for example, Pattern3 or Pattern22 with a typo.

So, the question is how to count the number of cells with invalid labels - have I got you correct?

If this is the case, you may use the following SQL query:

SELECT
COUNT(MATCH_REGEXP('Pattern', "(!Pattern1|Pattern2)")) AS 'Count'
FROM T*

Here we check the 'Pattern' column if its contents is not (!) "Pattern1" or(|) "Pattern2".

Or as a workaround you may use the CASE WHEN statement and assign 0 to valid statuses and 1 to all other labels (through the ELSE clause). Then you just sum this column and get the number of invalid labels.

Or are we talking about the case where you can have several statuses in one cell and they can be valid and not valid? 

|Pattern                |
|Pattern1 Pattern2 |
|Pattern1 Pattern3 |
|Pattern22              | 

What exactly do you want to count if this is the case?

[Maria Del Rosario Gonzalez]

Apologies for not clearly stating my use case!

Here is another attempt which will hopefully clear up what I am looking to do:

Users will typically enter in the Jira labels field Pattern1 OR Pattern2, etc. which I translate into an agreed upon value.  They are instructed to use labels that are already created for them.  For example, if Pattern1 is found in Jira labels then 50.

I use a case statement like so:

case
WHEN MATCH_REGEXP(T1.'Labels',"Pattern1") then 50
WHEN MATCH_REGEXP(T1.'Labels',"Pattern2") then 200
WHEN MATCH_REGEXP(T1.'Labels',"Pattern3") then 400
WHEN MATCH_REGEXP(T1.'Labels',"Pattern4") then 800
WHEN MATCH_REGEXP(T1.'Labels',"Pattern5") then 1600
else 0
END
as 'FieldName'

However, what I want to catch is an instance where the users have entered Pattern1 and Pattern2 (or any number of these) in the Labels field by mistake.

In that case, I just want to have an error message such as 'Dup Values found' in the column.

With my current approach using the case statement, the first match results in the first value match but there is no indication there are more than one Pattern labels entered in error.

Hopefully this a more understandable.  Thanks again in advance!

[Stiltsoft support]

Thank you for the clarification!

As I see, you have smth like this:

SELECT
CASE
WHEN LENGTH(T1.'Labels'->trim()) > 10 THEN "Duplicate labels"
WHEN T1.'Labels'->trim() = "Pattern1" THEN 50
WHEN T1.'Labels'->trim() = "Pattern2" THEN 200
WHEN T1.'Labels'->trim() = "Pattern3" THEN 400
WHEN T1.'Labels'->trim() = "Pattern4" THEN 800
WHEN T1.'Labels'->trim() = "Pattern5" THEN 1600
ELSE 0
END
AS 'FieldName'
FROM T*

WHEN LENGTH(T1.'Labels'->trim()) > 10 THEN "Duplicate labels" - here we count the length of our string. Here 10 is the length of "PatternXXX", so if the length of the cell contents exceeds this number, it means that there are at least two labels.

WHEN T1.'Labels'->trim() = "Pattern1" THEN 50 - here we don't need any regex or smth. You just compare your cell contents with this or that label from your predefined list in Jira.

->trim() - is used to skip special invisible characters. Here it may be optional as your users select predefined labels. You may delete it if you want.

Hope it helps your case.

[Maria Del Rosario Gonzalez]

You are brilliant!  This works like a charm.  Many, many thanks as always for superb support and very flexible product.