Use Text Input list to dynamically drive a formula that flags known DQ issues

[dnbShiner15]

Community,

I have to think this problem has been solved 10k times but I cannot seem to find a simple example of it being done.  Perhaps I am just using the wrong key words for my search.

The use case relates to flagging Data Quality issues.  I want to build a known static listing of data quality keywords that I then want to push into a formula dynamically.  Each row of the list would contain the keyword, such as "Testing", and the string function that I want to leverage.  Functions such as Contains, StartsWith, EndsWith, etc.

I have attached an example listing of keywords and functions that I want to use.

I can write an endless IF THEN ELSIF ENDIF statement but want to write one simple one and then dynamically push this listing of keywords through it to drive a desired result.  In my case the result would be a simple Y/N flag as to whether it passed the formula test or not.

Hopefully this makes a bit of sense and any direction you can point me towards would be greatly appreciated!

Thanks!

~Jason

Austin, Tx

[SPetrie · Accepted solution]

May not be exactly what you are looking for, but try Dynamic Replace.

You give it a set of tests, and if the test if true, it can replace a column with a value or formula.

Its a lot less messy than a long If then else function and easier to update.

[Yoshiro_Fujimori · Accepted solution]

Hi @dnbShiner15 ,

It may be overlapping with @SPetrie 's post, but I also think this is the case for Dynamic Replace tool.

Check function and Output message

(For your ease of understanding, I used the same field names as the Example workflow of Dyamic Replace tool)

You can tweak the "Value" field for the output message.

Input data

Configuration of Dynamic Replace tool

Workflow

Output

I hope this may be of some help.

Good luck.

[dnbShiner15]

Great suggestions and much easier to maintain than a long IF THEN ELSEIF statement.  Many thanks!!!

[dnbShiner15]

Another way of getting there is to add a record id to the dataset and then transpose the data.  You then join the transposed data to the series of "tests" that you want to perform.  Next you feed a REGEX_MATCH formula the set of tests and output cases where the result is TRUE.  You then crosstab the data back into its original form and join it back to its original.

Probably a much more convoluted way of getting there but it works!  Always learning!

Hopefully this helps someone out as using REGEX opens lots of doors and possibilities for testing strings, etc.

Enjoy!

Austin, Tx Y'all!

[Kavita_22]

Hello @Yoshiro_Fujimori

This suggested workflow will be beneficial for me. However, I would like to propose an additional requirement.

In my use case, instead of a single keyword like "Apple", I have a list of keywords recorded on a separate worksheet - for example, a list of 10 different fruits. Please note that the number of keywords might increase with every run.

Therefore, if the data contains any keyword present in the list, then the expected result should be 4.

Thank you for considering my request.

Thank You, 

Kavita