DATA CLEANING

[cc_jin]

how can we clean this data into column(names, supplemental pay:other,california income tax,california state disability insurance......),abc,bcd is the name

Thanks!

[ScottLewis]

There may be a more elegant way to do this, but I think this does what you're looking for. Keyed the example into an excel doc with some made up numbers.

Generally, the pattern is Get Rid of Noise, Apply the name to all rows, Split on the three sets of columns and then Union back together.

 

[cc_jin]

Thanks so much for your help! I will try this method!

[cc_jin]

Hey Scott, I wonder in your file, what is the F7 means? This is my file

[ScottLewis]

F7 is a generic column name. In the configuration of the input tool, there is an option for "First row contains data." We check this when were bringing in data without normal column headers and Alteryx just numbers the columns F1-FX. Generally, I would suggest renaming columns to something meaningful as soon as they've been cleaned to the point where each column has a distinct meaning. In this case, that doesn't really happen until we've pulled off the ABC type names and split the columns, so I did the renames there.

Separately, the sample above didn't have the total rows at the top. You'll want to add a Sample tool configured to Skip First N Rows (N=3) to the beginning of the workflow to peel those off before the rest of the logic runs.