Solved

Divide results from Invalid Rule column in Monitoring Project Report Export into multiple columns

  • 16 March 2023
  • 2 replies
  • 132 views
A
Userlevel 3
Badge +2

The idea is to divide the invalid_rules column as obtained in export for Monitoring Project into two columns- one containing the rule name and the other containing the explanation; and implement this for all the rules mentioned in a cell.
For example, 
The below cell would be divided into invalid_rule_name column containing two records-validation [USA] State and calidation[Noth America] Phone Numbers, and invalid_rule_explanation column containing explanation for the respective rules.


The challenge I am facing here is that the rule ID mentioned in Monitoring Project report export is not the same as the rule ID mentioned in the list of rules I export; which I am trying to use to identify the rule in invalid_rules column from Monitoring Project report.

Please help me find a solution for this

icon

Best answer by AKislyakov 22 March 2023, 09:48

View original

2 replies

A
Rank
Userlevel 4
Badge +4

Hi aiwalia,

You can divide single invalid_rules_explanation colun into two (one containing only rules names and the other containing only explanation codes) with a regular expression.
 

For rule names:

substituteAll(@"(?<=,|^)(.*?):\(?.*?\)\(?[0-9a-f-]+\)\:([A-Z0-9_]+)(?=,|$)", "$1", invalid_rules_explanation)

For explanations:

substituteAll(@"(?<=,|^)(.*?):\(?.*?\)\(?[0-9a-f-]+\)\:([A-Z0-9_]+)(?=,|$)", "$2", invalid_rules_explanation)

The expression in both cases is the same, we just take different capturing groups as a result. Here are details on the expression itself.

(?<=,|^)            # positive lookbehind to match a comma or start of the string
(.*?)               # non-greedy capture group to match rule name
:\(.*?\)            # match a colon, followed by an opening parenthesis, column 
                      names, and a closing parenthesis
\([0-9a-f-]+\):     # match a hexadecimal UUID wrapped in parentheses
([A-Z0-9_]+)        # capture group to match an explanation code consisting 
                      of uppercase letters, digits, or underscores
(?=,|$)             # positive lookahead to match a comma or end of the string

 

 

A
Userlevel 3
Badge +2

Thanks a lot @AKislyakov . This has helped me to get to the end solution for Export Report format. This was extremely helpful. 👍🏻

Reply


Terms & ConditionsCookie settings