Data Cleansing - Fields arrange

[dunkindonut7777]

Hi i have a data that have disorganized details. i want to arrange it based on the sample output attached. Can you help me with this?

[davidskaife · Accepted solution]

Hi @dunkindonut7777 

Wow that is a mess...

Here is my attempt at it. It's on the assumption that the extract will be fairly static, with no additional fields, and the Source Reference always starts with JV

I've annotated it to help you, and workflow attached

Note: you cannot have field names the same, so the accounting data fields 'Source' and 'Date' have been called 'Source_xx'

[dunkindonut7777]

Hello this is great. It works, however, i forgot to add some off the details on it. There are repeating entries on it with different journal number. I can't figure out how to include or arrange those other journal entries, with journal number 2-5. (actually there are a lot of journal numbers, I only included journal 1-5. Can you please help me with this one? with the same format in the sample output attached?

[KGT]

In order to have it apply to multiple journal entries then the easy way is to take what @davidskaife built and group it. No need for a batch macro as you just need to define the groupings up front and then carry that through.

So, use a multi-row up front to create a field called [JournalNumber] with IIF(Contains([F1],"Journal number :"),[Row-1:JournalNumber]+1,[Row-1:JournalNumber])

You now have a field that acts as a grouping for each journal. In the sample tools, you can select this grouping. Maybe the filter will need work and the Find/Replace tools may need to be replaced by Joins including [JournalNumber] as a join field. The rest looks alright on the screenshot, but you may need to play with it a little. The grouping field is the key!

Also, one thing that may not have been in the original solution, but would require updating a bunch to change, I would ALWAYS select "First Row Contains Data" on the input tool for this type of operation so that the fields are named F1,F2 etc.

[dunkindonut7777]

Sorry, I can't visualize it really. How can group the transformation without using the sample tool. 

[KGT]

Not sure what you mean by "How can group the transformation without using the sample tool."

Before the sample tool, you would create the grouping field, and then select that as a grouping field in the sample tool.