Skip to main content
Solved

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

A
Data Pioneer
Forum|alt.badge.img+3

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

Best answer by AKislyakov

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

 

 

View original
Catherine
Faye
2 people like this
  • Catherine
    Catherine
  • Faye
    Faye
Did this topic help you find an answer to your question?

2 replies

A
Ataccamer
Forum|alt.badge.img+2
  • AKislyakovAtaccamer
  • Ataccamer
  • 149 replies
  • Answer
  • March 22, 2023

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
A
2 people like this
  • A
    Adrian Anderson
  • A
    aiwalia
A
Data Pioneer
Forum|alt.badge.img+3
  • aiwaliaData Pioneer
  • Author
  • Data Pioneer
  • 15 replies
  • March 24, 2023

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

Reply


Most Liked this week

Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings