Hello Experts,
Currently my input data has the below four columns.
I'd like to ensure whenever there is NC and 7numeric values next to it in columns description and summary - it should result out in a new column with KEY values or say No Match.
Input Data :
Project | Key | Description | Summary |
Railways | RL-1567 | Priority tickets to be incorporated | NC5847865 tag and include |
Engineering | EG-2564 | Inclusion of NC8745624 | process files within NC8745624 |
Science | SC-6598 | Fix Data NC9875452 | terminatedteamhasidentity |
History | HS-0258 | corporatedata into asssement incorporated | fix missing field NC0254563 |
English | EG-7896 | process not working | prepare test for NC9965523 |
Math | MATH-4569 | set up incidents of process | lake streaming with details in NC0245635 |
Expected Output:
Project | Key | Description | Summary | Results |
Railways | RL-1567 | Priority tickets to be incorporated | NC5847865 tag and include | RL-1567 |
Engineering | EG-2564 | Inclusion of NC8745624 | process files within NC8745624 | EG-2564 |
Science | SC-6598 | Fix Data incorporated | terminatedteamhasidentity | No Match |
History | HS-0258 | corporatedata into asssement incorporated | fix missing field NC0254563 | HS-0258 |
English | EG-7896 | process not working NC9965523 | prepare test incline | EG-7896 |
Math | MATH-4569 | set up incidents of process | lake streaming with details | No Match |
Solved! Go to Solution.
Hey @geeklarokcmie, we can use RegEx to look for certain string patterns so I went down the route of checking if there's a match of NC followed by 7 numerics with the following approach. Only thing is the last record in your scenario above - according to the logic this should pull the [Key] through but you have 'No Match' in your expected output - is this an error?
Hi that could be done wiht the formula tool and regex match
If regex_match([description],".*NC\d{7}.*") or regex_match([Summary],".*NC\d{7}.*") then [Key
else "No match" endif
Hi @geeklarokcmie, thank you for your question!
Do note -
Now, important to notice - the two excellent answers above utilize different functions.
Having said all that - attached is a solution that takes both approaches into account:
notice that I didn't write ">0" in the countmatches option - that's due to it either being 0 or 1. A logical expression that equals 1 is evaluated as True. Should work for all values bigger than 0
Hope that helps!
@DataNath I completely missed the part where the input data columns for summary and description were not as same as output data. I should have corrected that before posting. Appreciate your solution as it worked.
@AmitMiller Appreciate the clarification and providing two different solutions along with the reasoning. I will run your workflows to understand how it satisfies my data requirements.