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

Clean and normalize data

BrotherMAK
5 - Atom

Hi folks,

        I've done some pre-work cleaning data as it comes in via query, though there are outliers that I don't want to continue to accommodate via SQL. I'd rather do any additional cleansing/normalization of the data using the Alteryx tools, if possible. 

 

When certain text shows up in a field, I need it removed....THEN normalize what is left over into a consistent format, with leading zeros.

 

I believe data can be removed via: REPLACECHAR(REPLACECHAR([Yourfield],'"',''),"'","")

 

Pending that works, how would I have the tool (or another in a following step) analyze and append needed leading zeros.

 

Any help is appreciated. Thanks!

 

.: Mike

 

INPUTDESIRED OUTPUT
ABC1234001234
123456123456
QWER12345012345
POI 012345012345
7 REPLIES 7
DavidP
17 - Castor
17 - Castor

You can use padleft to add the zeros.

 

For removing characters, here are 2 methods. Workflow attached

 

DavidP_0-1581631941746.png

 

fmvizcaino
17 - Castor
17 - Castor

Hi @BrotherMAK ,

 

Attached is an example showing how to do it with regex tool and also another way of doing with data cleansing tool.

 

Let me know if this works for you.

BEst,

Fernando Vizcaino

fmvizcaino
17 - Castor
17 - Castor

@DavidP , the exact same solution, both suggesting two ways of doing it!!!! Incredible!!

DavidP
17 - Castor
17 - Castor

@fmvizcaino, great minds!

Blake
12 - Quasar

Here is one way you can solve this issue. 

 

Blake_0-1581632112139.png

 

Regex Configuration

Blake_1-1581632154864.png

 

Formula configuration

 

Blake_2-1581632184180.png

 

 

 

 

Blake
12 - Quasar

ha a little bit late @DavidP @fmvizcaino but can I still join the great minds party?!  

BrotherMAK
5 - Atom

Fantastic!  Thanks everyone!  Looks like everyone had more or less the same idea. 

 

I was about to punch myself as I wasn't getting the desired result until, like an idiot, I realized I wasn't replacing my key column as the output of the flow.  Did that and getting the hits I was looking for.  This was perfect.  (PS...I went with the Data Cleansing tool as it was much simpler for what I needed.  I'll keep the regex in mind if I end up with more complex stuff, that's what I used in the originating query)

 

PS.  Don't mind the million browses and multiple outputs 🙂  Doing some checks along the way.

 

Thanks again. 

 

.:MAK

 

BrotherMAK_0-1581642440284.png

 

Labels