manipulation: rows to columns problem - any ideas or help?

Hi guys,

I try to think about an approach to column to rows problem and can’t seem to find an idea.
I have this type of data: it’s a whole hierarchy in one column that needs to be divided
image

And i need to manipulate it to populate this in other columns like so:

Any ideas how to approach this?
I know I can set an index to every row but the dataset itself is to unstable to do so :frowning:

I usually split the columns (Column Splitter node) on the tab (copy and paste one of the spaces to make sure you match it). I like to manually set the array with some extra levels to avoid errors in the future if additional child account levels are added. You can use missing values to fill down from the previous, but you will have to use an if statement to ensure the fills do not extend past the appropriate part of the hierarchy. After that you only need to rename the columns.

I have components to do this, since I end up doing it all of the time to rebuild chart of account structures in KNIME. I will try to post one that fits when I am back in the office tomorrow.

2 Likes

I made one of my Chart of Account mappers into a generic component for you. Just pick your target column and paste in the value of your delimitator. (Aka - the number of blank spaces used to show a level change in your case)

1 Like

@Nemrod

FYI - You can ignore all of the settings at and below “Secondary Split on Hierarchy Map (ie: Acct # / Name)”

I went ahead and started building in some additional capabilities so it can work as an all around Chart of Accounts breakout tool.