Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Searching and reporting on special characters in a spreadsheet

Highlighted
7 - Meteor

We have spreadsheets for which we have to check for special characters amongst the data. We have to check for the following

 

&,  &#,  /~*,  --,  <,  >,  ‘,  “,  ’,  Carriage Return,  Line Feed,  #. \. '

(Commas excluded)

 

I would like to report in the form of a error messages like:

 

AG18 - Special Character "&'" found 

M14 - Special Character "Line Feed" found 

T16 - Special Character "Line Feed" found

 

(We can't modify the spreadsheet without permission, we're tasked with finding things like this and reporting back)

 

The only way I can think of doing this is to have a filter for each character and for each column. Since our spreadsheets go to Column AH I can't imagine creating 422 filter tools for each tab. There's got to be a better tool for something like this.

 

For all my other checks I do a filter tool, send it to a "Report Text" tool which provides the error message and on it goes to a Union, and a render.....

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@cjames728,

 

Think TRANSPOSE!  If your data is named with column headers, here's an example:

 

KEY|DATE|NAME

101|2017-12-19|Mark

202|2017-12-19|Mark & Gina

 

You can use a RECORDID tool and get a Row number and then TRANSPOSE the incoming data on ROW (as the key) and all other fields as data.  Now your output will be:

 

ROW|NAME|VALUE

1|KEY|101

1|DATE|2017-12-19

1|NAME|Mark

2|KEY|201

2|DATE|2017-12-19

2|NAME|Mark & Gina

 

Now you can use a FORMULA to create a new field via regular expressions to see what weird stuff you found.  FILTER on IsNull([STUFF]) and take the False output to see what was found where and in which rows.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
7 - Meteor

Absolutely Brilliant! That solved another (simpler) problem of how to report columns of missing required fields as well.

 

Many thanks!

Labels