Cleansing of data

[Kaustubh17]

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

[danilang · Accepted solution]

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. 

[Elias_Nordlinder · Accepted solution]

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

[Kaustubh17]

Hi @Elias_Nordlinder 

Thanks for your help. Your explanation help me alot. I accepted your solution.

Thanks,

Kaustubh

[Elias_Nordlinder]

Great,

I am happy to help 🙂

Have a great day.