Data Cleansing | Arranging of Fields and Structure

[dunkindonut7777]

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?

[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/...

[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