Regex Workflow

[Suryachoppakatla]

Hello Friends,

I am struggling with one of the issue where I need to write a formula which should able to determine the repeating same TIN numbers in a column. If the number is identical and repeated more than once, then the formula should be able to pick and validate it as "Invalid" else "Valid"

I wrote below formula, but it did not worked. NOTE: The TIN numbers are 9 digit 

IF REGEX_Match([Recipient's U.S. TIN], ".*(?:(\d)\1{8})") THEN "Invalid" ELSE "Valid" ENDIF

Attaching the screenshot of the data for quick reference. Any help is highly appreciated

Regards,

Surya

[ShankerV · Accepted solution]

Hi @Suryachoppakatla 

Please find the expected solution.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[Suryachoppakatla · Accepted solution]

@ShankerV . Thank you very much for your help. This looks awesome, however am trying to embed this into Analytical app. Let me try this and update you

However, am very thankful to you

Best Regards,

Surya

[ShankerV · Accepted solution]

Hi @Suryachoppakatla 

Sure, keep me notified. Also let me know if you have any more questions.

[Suryachoppakatla · Accepted solution]

Hello Shankar,

I tried embedding your solution into Analytical app, which I am building. The background of this App is that we get the attached template (input files) from different clients, meaning the data in the input file is different, however template (headings) remain same as this is standard template sent to all clients to fill in their own details

Not sure what wrong am doing in writing the formula, I am not getting the desired result as you got (which is exactly I wanted) after running the app

I attached my App workflow and output file and Input file for your quick view and run it at your end

Couple of points I would like to let you know here.

1. Cell C7 & C8 should be identified as "Individual" not sure why the formula not recognized, however C9 & C10 was correctly recognized as "Entity" which is good

2. Regex result in (B7,B8 & B9) results are correct, but again B10 result is wrong

I spent good amount of time on this, but could not achieve your answers :). Can you please validate and correct my workflow

Thanks in advance

Surya

[ShankerV · Accepted solution]

Hi @Suryachoppakatla 

Hope you are having a good day!!! and enjoyed your Christmas with family and friends!!!

Here are my observations!!!!

Issue 1:

As per your request, you need to identify the invalid TIN if 111-11-1111 or 222-22-2222 or 99-9999999 or 88-8888888 etc.

But you have inherited my solution here. i.e after the formula tool.

I have checked your Formula tool, where you are modifying the incoming TIN to valid or invalid.

Then this stored value in Recipient's U.S TIN which is Valid and Invalid goes for the Regex, which is incorrect.

Also we are storing the result in TIN Repeat Validation and in the Select tool you have deselected it. Then the output is not displayed.

Even it displayed it will be Invalid for all as it takes the wrong Regex calculation.

I have modified the analytical app to get the expected solution.

Input is:

Output is:

Issue 2 and Issue 3:

The below 2 issues are totally different which I can address it separately if you confirm the above output is as expected.

1. Cell C7 & C8 should be identified as "Individual" not sure why the formula not recognized, however C9 & C10 was correctly recognized as "Entity" which is good

2. Regex result in (B7,B8 & B9) results are correct, but again B10 result is wrong

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[Suryachoppakatla · Accepted solution]

US States

[ShankerV · Accepted solution]

Hi @Suryachoppakatla 

Please find the revised file with the expected output.

Many thanks

Shanker V

[ShankerV · Accepted solution]

@Suryachoppakatla 

Issue 1: Fixed

[ShankerV · Accepted solution]

@Suryachoppakatla 

Issue 2: Fixed