What is best practice for data integrity testing?

[wasehkhan]

Hello,

I am new to Alteryx and I was wondering if there is a best practice to identify following issues:

Currently, I have over 30 to 50 fields to validate and I am using Formula option for each field which is very time consuming.  Every field in the dataset has its own limitations. 

1.  Length Limitation = 50 2.  No Blanks are allowed 3.  No special Characters	1.  Length Limitation = 5 2.  No Blanks are allowed 3.  No special Characters 4.  Format should be H0000	1.  Length Limitation = 10 2.  No Blanks are allowed 3.  No special Characters 4.  Format should be YYYY/MM/DD 5.  "NA" is allowed	1.  Length Limitation = 2 2.  No Blanks are allowed 3.  No special Characters 4.  Possible options are; NA, CP or NP
Customer Name	Customer ID	Invoice Date	Invoice Type
John Smith*	H1234	2018/11/01	CPN
	H/123	1/2/2019
Chris C.	H23	NA	NP

Thank you for helping. 

Regards,

Waseh

[afv2688]

In the developer tools, the message tool is very helpful for this matter. You can configurate it to just give you a message or stop the workflow and report the issue to you.

In this example I set a table with a null value. Really easy and intuitive to do

[wasehkhan]

Thanks for quick response.   Actually, I have to report issues to the business owners identifying which column and records has blank fields, over the limit length, any invalid characters or values and invalid format.   I already created expressions for each field looking for issues listed above.  Please see the screenshot of the expression. 

I am just wondering if there is a easier way to test each field individually and identify what types of potential errors may occur

[afv2688 · Accepted solution]

Not that I am aware of. Maybe there are some community macros that do that work. You could also create a macro that does this work for you. That way once programmed, you wouldnt need to apply it again, just configure it for each field needed.

[wasehkhan]

Thanks Asteroid!  I am new to Alteryx so I know it would take some time to learn on the tricks (macros)