Cleansing of data

Dear Alteryx Team,

Can someone help me how can I clean this data based on the column headers. you guys can see that few dates are written in correct column but few are in different column. I want to set the value to the correct column. how I can set values to the correct fields. I want all store , date, sales, product id values in there relevant fields.

Thanks,

Kaustubh

Sales data.xlsx

Workflow

Accepted answers

danilang

Hi @Kaustubh17

Before you can do this you need to provide the rules that define the various fields. From looking at your data it looks like

1. Dates contain a "-"

2. Sales contain a "." period

3. StoreNumber has 3 characters

4. ProductID has 5 characters.

Is this a complete list of rules that we can use to parse out the data? Are there any exceptions?

Dan

Elias_Nordlinder

Hello @Kaustubh17 ,

You can use the reasoning by @danilang like this:

1. Add a RecordID to use in Cross Tab later.

2. Transpose the data to be able to easier create the formulas in @danilang post.

3. Use a Formula tool with the formulas from that post to rename the "Name" column correctly:

IF CONTAINS([Value],"-") THEN "Date"
ELSEIF CONTAINS([Value],".") THEN "Sales"
ELSEIF LENGTH([Value]) = 3 THEN "StoreNumber"
ELSEIF LENGTH([Value]) = 5 THEN "ProductID"
ELSE NULL()
ENDIF

4. Use a Cross Tab Tool to move back the data to the original format again

with the updated Name Column.

//Let me know if this solves your problem
Regards
Elias

Cleansing of Data.yxmd

All comments

danilang

Hi @Kaustubh17

Before you can do this you need to provide the rules that define the various fields. From looking at your data it looks like

1. Dates contain a "-"

2. Sales contain a "." period

3. StoreNumber has 3 characters

4. ProductID has 5 characters.

Is this a complete list of rules that we can use to parse out the data? Are there any exceptions?

Dan

Kaustubh17

Hi @danilang

This is a complete list of rules there is no other data. sales data is a floating data that's it. can you provide me a regex so that i can separate the based on their values and put them in a relevant columns i am facing issue. that will be very helpful if you will provide me a workflow and regex. but how i can put the values in there relevant columns because few dates are in store column and few store id are in date column how we can fix this.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703