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

Cleansing of data

Kaustubh17
8 - Asteroid

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

5 REPLIES 5
danilang
19 - Altair
19 - Altair

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
8 - Asteroid

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
11 - Bolide

Hello @Kaustubh17 ,

 

You can use the reasoning by @danilang like this:

 

Elias_Nordlinder_0-1629621433846.png

 

 

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

 

Elias_Nordlinder_0-1629621806490.png

 

 

 

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

with the updated Name Column.

 

 

Elias_Nordlinder_1-1629621450030.png

 

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

Kaustubh17
8 - Asteroid

Hi @Elias_Nordlinder 

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

 

Thanks,

Kaustubh

Elias_Nordlinder
11 - Bolide

Great,


I am happy to help 🙂

Have a great day.

Labels