Data Prep

[nursharifah]

Hi Everyone, i am new to Alteryx Designer. Need all of you to help me on this.

Attached is the excel file. First sheet is the example of raw data and the second sheet shows how i wish my data will looks like. i try to use text to column, i but it cant be used as the first column contain more than one delimiter. 

Thank you in advance.

[Luke_C]

Hi @nursharifah 

Here's something to get you in the right direction:

1. Remove null rows and columns
2. Fill down customer info
3. Filter out null records
4. Update column headers
5. Parse customer info

[DawnDuong]

hi @nursharifah 

Looks like we are in the same field (Finance!) and I feel you will grow to love alteryx.

I often deal with this type of data format, processing ledger data etc. and make it cleaner for further processing / analysis.

For this case, Regex is more suitable than Text To column.

I would highly recommend that you check out the interactive lessons, the one marked as "for excel users". The definite "must-have" in my opinion include the Input/Output, Preparation, Join, Parse, and Transform groups of Tool.

In this case, there are 2 different things that you want to achieve:

1) processing the "data bits", which is relatively straightforward, and likely your main concern is to get rid of the noise.

2) processing / splitting the customer ID/customer name. 

I tried to split the 2 things into 2 different branches. The key is you can join the 2 processes back at the end with an append field so that you have one unified table that you can analyse further.

Cheers,
Dawn.

[nursharifah]

Hi @DawnDuong,

Thank you soo much, its help me to have a clear picture on how to deal with this kind of dataset.

Appreciate your help!

[nursharifah]

HI @Dawn,

Im facing an issue which actually the dataset i attached is not completed. means that, i still got some data at the bottom of the row in the excel file.you can refer to file i attached. when i try, i have the repetitive column name like this:

[nursharifah]

hi @luke

Im facing an issue which actually the dataset i attached is not complete. means that, i still got some data at the bottom of the last row in the excel file. you can refer to file i attached. when i try, i have the repetitive column name like this:

[Luke_C · Accepted solution]

Hi @nursharifah 

Simply add a filter after and them out. For example you could do a filter on Stock_code != 'Stock Code'