Multiple fields check

[Ramu]

Dear All,

I have a scenario where i need to find the JIRA Tickets having more than one Labels of the same category (not duplicates .. but of same category as explained below) ..

i have a JIRA Table of 100+ tkts

Scenario:

- check the JIRA Tickets having more than one 'Testing' Labels

(we have many labels like 'Testing-abc', 'Testing-ert', 'Testing-uyi' , 'Testing-456' ... etc .. )

SO,

if any JIRA Ticket has more than one  'Testing' label

(for example, a tkt having Testing-abc and Testing-ert ..both .. )

THEN, i need to display those Tickets as 'Multi Testing Label' ones ..

im trying a code in my Confluence Table Transformer as

 //// CASE

       When T1.'Labels' LIKE "%Testing%" AND T1.'Labels' LIKE "%Testing%" AND T1.'Labels'             LIKE "%Testing%" Then "Multiple Testing Labels"

          END AS 'Multi Testing Label'   ////

but this is not working ..

hence, reaching out to you .. please help ..

[Daniel Berežnoi]

Hi Ramu,

This is Daniel from Stiltsoft team, the creators of Table Filter, Charts & Spreadsheets for Confluence.

This is a somewhat complex use case for the Table Transformer, so the solution may not be as straightforward as desired.

One approach is to use two Table Transformers. Please refer to the screenshot below (taken in Cloud, but the setup is identical in the Data Center version):

In Section A, I used the following query (based on an example from our documentation): 

SEARCH / AS @a EX(SPLIT_VIEW('Labels', ",")) / 
RETURN(@a->'Key' AS 'Key', _ AS 'Label') FROM T1

This splits the Labels column into individual values to make counting occurrences easier.

In Section B, I used the following:

SELECT T1.'Key', TD2.'Testing Label Amount' FROM T1
LEFT JOIN
(SELECT 'Key', Count('Label') AS 'Testing Label Amount' FROM T2 
WHERE 'Label' LIKE "Testing%" GROUP BY 'Key') AS TD2
ON T1.'Key' = TD2.'Key'
WHERE TD2.'Testing Label Amount' > 1;

This counts the number of Testing category labels for each Key, then joins that result with the main table. The final filter retains only entries with more than one Testing label.

Let me know if this solution works for you.

Daniel

[Ramu]

Hi Daniel,

Thank you for the response .. :)

Given i have multiple other conditions as well in my Table Transformation, splitting this logic alone and having another Table and modifying is kinda time taking one .. as i need to change other portions of my code as well ...

instead of WHEN .. i have tried with IF

CASE
IF T1.'Labels' LIKE "%Testing%"   AND   IF T1.'Labels' LIKE "%Testing%"
THEN "Multiple Testing Labels"
END IF
END AS 'Multi Testing Labels'

..... not working 

However, let me check and try ... and will keep you posted if happening ..

In the interim time, in case,

if you find any other alternate solution.. something like using multiple(nested) IF..THEN condition etc .. (tried but its not working) ..

pls do let me know ..

Cheers ...

[Daniel Berežnoi]

Hello Ramu,

I found out that it is possible to utilize the MATCH_REGEXP function to count the number of labels.

You can use the following SQL query:

SELECT * 
FROM T1 
WHERE 
MATCH_REGEXP('Labels', "Testing", "g") -> length > 1;

Let me know if this works for you.

Daniel

[Ramu]

Hi Daniel,

Thanks again ..

This one 

CASE
MATCH_REGEXP(T1.Labels, "Testing", "g") -> length > 1 THEN 'Multiple Testing Labels';
END AS 'Multi Testing Zones'
FROM T1

returns the error "SyntaxError: Parse error on line 1: ... "g") -> length > 1 "

as it couldnt recognise/accept the length > 1 ..

Actually, before as well, i was trying diff REGEX combinations (refer match_regex ) ..

in addition to the aforementioned and regex, these are the other samples i tried so far:

/***********************
CASE
WHEN MATCH_REGEXP(T1.'Labels', "Testing+") THEN 'Multiple Testing Labels'
END AS 'Multi Testing Zones'
FROM T1
*************/

/********************
IF T1.'Labels' LIKE "%Testing%" AND IF T1.'Labels' LIKE "%Testing%"
THEN 'Multiple Testing Labels'
END IF
END IF
***********************************/

/*****************
BEGIN
IF ((T1.'Labels' LIKE '%Testing%') AND (T1.'Labels' LIKE '%Testing%'))
Then T1.'labels' = 'Multiple Testing Labels'

END IF
END
***********************/

Having said and tried all these things multiple times for days now, i request you to please send a call invite to connect and get this sorted out .. i believe this takes one quick call to get it fixed .. Please consider and share the invite.

[Daniel Berežnoi]

Hi Ramu,

Please note that the structure of your CASE expression is incorrect. The correct structure would be as follows:

CASE
WHEN condition1 THEN result1
ELSE result
END

Try the following expression:

CASE
WHEN MATCH_REGEXP(T1.Labels, "Testing", "g") -> length > 1 
THEN 'Multiple Testing Labels'
END AS 'Multi Testing Zones'

Please note that this expression will not return anything in case 'Multiple Testing Labels' column does not exist.

Let me know if this works for you.

Daniel

[Ramu]

Hi