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
Solved! Go to 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
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.
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
Thanks for your help. Your explanation help me alot. I accepted your solution.
Thanks,
Kaustubh
Great,
I am happy to help 🙂
Have a great day.