Data Cleansing | Arranging of Fields and Structure

I have a dataset (sample input) that is unorganized, with misaligned fields and columns not arranged properly. I need it to be correctly formatted into columns, as shown in the sample output. The sample output only displays journal number 1, but I want the formatting to apply to all journal entries. Can you help me with this?

sample input.xlsx

sample output.xlsx

Amazon S3

AMP Engine

Adobe

AMP-Engine

Accepted answers

davidskaife

Hi @dunkindonut7777

Try this, i've assumed that the other notation fields are in one for now but let me know if anything needs to change

As before all annotated with what each tool is doing, and i've added in containers where i've changed configurations or added new tools

Fields Arrange V2.yxmd

All comments

KGT

FYI, before anyone spends half hour building this out from scratch, the question is around dealing with the multiple journal entries, rather than just parsing 1 journal entry.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Data-Cleansing-Fields-arrange/m-p/1375795

dunkindonut7777

Sorry, I can't visualize the one you mentioned. I find it really difficult to apply it to multiple journal entries.

dunkindonut7777

Can you help me with this one please? a sample workflow would be a great help

davidskaife

Hi @dunkindonut7777

I'm working on a revised solution which i'll post in the original thread. The addition of multiple journals requires a few changes as @KGT highlighted in his post.

dunkindonut7777

thank you!

davidskaife

@dunkindonut7777

What's your expected output for the other journals?

dunkindonut7777

the same with the sample output attached. But I find it hard to separate the transformation because in the initial sample, I only attached the journal number 1, however there are other journal entries which I find it hard to transform.

Expected Output: The same with the output sample attached but with other journal numbers 2-5

davidskaife

@dunkindonut7777

Might not have been clear, for journals 2 onward the 'Notation' field contains multiple values which are separated by : such as sourcecode, AP type code etc whereas for journal 1 it was one long string only

Do you need them all in the notation field so its "Sourcecode : AP Type code : DSB Group number : 14" or something else?

davidskaife

Hi @dunkindonut7777

Try this, i've assumed that the other notation fields are in one for now but let me know if anything needs to change

As before all annotated with what each tool is doing, and i've added in containers where i've changed configurations or added new tools

Fields Arrange V2.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703