How to have Alteryx perform data validation on all rows?

I have a block of data in excel (117 columns) with potentially thousands of records that I need to perform validation on for those columns. For example: I need to check the maximum length for all columns to ensure they don't exceed the specified character limit for that column.

My current approach is to define the variable as a constant as a User type, then check via a formula: IF Length([Field1])=[User.Length_Field1] THEN "Valid" ELSE "Invalid (Check Length)" ENDIF

That outputs to a new column of course, but is this the best way to do this? I have other validation to perform on these fields and I don't want to end up with hundreds of constants and hundreds of extra columns/fields.

I'm new to Alteryx, so appreciate any advice.

Thank you.

Data Investigation

Accepted answers

KGT

Are you validating the records or the columns?

If you are validating the columns then I think you would need an extra column for each validation. If you are validating the records (record fails if one column fails) then you may only need the one validation column, but where are the rules and audit trail? How much audit you need or info you need to pass back to the user guides how much "working" you need to show.

The other option you can do is to write out the validation rules in a table and then "apply" that. I've done this before with a column that has a regex expression for the test for instance.

For instance, a table set out like this:

Check the attached for this method. You're not dealing with huge data and so even 10k records transposed with 117 columns will only be 1.17m...

Validating columns.yxmd

All comments

OTrieger

@GavriloPrincip

Potentially you can do it with Multi Field Formula, however the question will be how many different data validation you will need to do. If it is only for the field length then you can do it or on the cells themselves or create a new fields to indicate the valid not valid. and
How many different validation will you need to do and are the standard, meaning that Length of the data will be ne to be 20 in all of them etc.

So form your question is very hard to know what you will need.

GavriloPrincip

Hey OTrieger,

Here is a good spread of my use case for a few columns that I'll need to do across my entire dataset:

Excel Columns: Column A, Column B, Column C

Column A Criteria:

Max Length: 6
Cannot be empty

Column B Criteria:

Max Length: 20
Can be empty

Column C Criteria:

Max Length: 2
Must have a value if Column B is empty

I hope this helps better describe what I need to solve.

KGT

Are you validating the records or the columns?

The other option you can do is to write out the validation rules in a table and then "apply" that. I've done this before with a column that has a regex expression for the test for instance.

For instance, a table set out like this:

Check the attached for this method. You're not dealing with huge data and so even 10k records transposed with 117 columns will only be 1.17m...

Validating columns.yxmd

GavriloPrincip

I'm validating the records. It looks like that table with rules is what I need. I'm still learning, but will try this method and see how it goes.

thank you!!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703