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 NameCustomer IDInvoice DateInvoice TypeJohn Smith*H12342018/11/01CPN H/1231/2/2019 Chris C.H23 NANP Thank you for helping. Regards, Waseh

What is best practice for data integrity testing?

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

Reporting

Accepted answers

afv2688

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.

All comments

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

Untitled1.png

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

Formula Expressions.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703