Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Character Length Errors

reginawhelan
8 - Asteroid

Hi, I've built a flow that validates & formats the data appropriately to upload into a system.

 

There are 81 columns that all have unique character limits, for example:

Field 1 must be 9 characters

Field 2 allows for up to 50 characters (including blank spaces)

etc.

 

I would like to have a validation that errors out any fields that exceed the character limit but in just one column (I don't want 81 error columns). I would like to have one column that identifies the specific cell/s that exceeded the limit. For example:

Column 1/Row 29 - exceeded character limit 

Column 1/Row 29 & Column 12/Row 29 - exceeded character limit

 

I've tried the following:

Created multiple formulas (one function), as follow:

if Length([Field 1]) > 50 then "Yes" else "No" Endif

Then another formula function following with:

IF [Field 1] = "Yes" THEN "Field 1" or if([Field 1] = "Yes" and [Field 2] = "Yes") then "Field 1 & Field 2" ELSE "No" ENDIF

 

The second formula has Malformed IF Statement.

 

Is there an easier way to identify these errors? Without using so  many if/and statements?

 

7 REPLIES 7
bpatel
Alteryx
Alteryx

hi @reginawhelan ,

 

what about trying something like this - taking your 81 columns, then transpose and have a second table or file with all the corresponding character limits and do the same to join and then have one simple formula

bpatel_2-1593108613909.png

 

Hope this helps and is what you are looking for

 

 

 

reginawhelan
8 - Asteroid

Thank you. I'm still having some difficulties and hoping you can help.

 

I am getting an "invalid type in operator" error message.

 

I used the exact same formula you used in your example and tried changing the Type to V_WString & String but still getting the error. 

 

reginawhelan
8 - Asteroid

Hi again, please disregard my last message. I removed all the Select functions and it now works. Thanks again!

reginawhelan
8 - Asteroid

Hi there, I found your solution very helpful and have replaced a few formulas within my flow with it. Do you know if I could read multiple values? For example:

 

The gender could be Male or Female. I would want to identify anything that isn't Male or Female. 

 

I've tried numerous methods to read both values but not having any success.

 

The flow right now only looks at the first value.

 

Thanks 

 

 

bpatel
Alteryx
Alteryx

hi @reginawhelan,

 

do you have an example of the what the data looks like? 

reginawhelan
8 - Asteroid

Hi, I want to ensure the customer is either Male of Female. It's ok if the field is blank as the customer may not have a spouse or children.

 

The errors I want to identify are red in the following chart:

 

Customer IDGenderSpouse GenderChild 1 GenderChild 2 GenderChild 3 Gender
Customer 1MaleFemale   
Customer 2MaleFemaleMalw  
Customer 3MaleFemaleFemaleFemale 
Customer 4MaleFemaleFemaleMaleFemale
Customer 5MaleFemaleFemale  
Customer 6FemaleFemaleFemale  
Customer 7FemaleMale   
Customer 8FemaleMale   
Customer 9N/AMale   
bpatel
Alteryx
Alteryx

hi @reginawhelan ,

 

i hope this is helpful and what you are looking for. you could do multiple different things but here are two suggestions i came up with:

bpatel_0-1593545876923.png

or 

bpatel_1-1593545892889.png

 

Labels