Data Prep

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.

QUESTION.xlsx

Developer Tools

Designer Cloud

Date Time

Help

Data Investigation

Chained App

Behavior Analysis

Accepted answers

Luke_C

Hi @nursharifah

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

All comments

Luke_C

Hi @nursharifah

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

Remove null rows and columns
Fill down customer info
Filter out null records
Update column headers
Parse customer info

New Workflow5.yxmd

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.

Response to NurSharifah April2022.PNG

Cheers,
Dawn.

Response to NurSharifah April2022.yxzp

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:

QUESTION.xlsx

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:

QUESTION.xlsx

Luke_C

Hi @nursharifah

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703