Split data based on categories

Hi everyone,

I am kindly requesting your help to perform the following:
I have one excel table with accounting data with the following columns:
Expense Category. Expense sub-category. Amount
Refund VAT. $100
Expense Dinner $50
Expense Transportation $150
Refund. VAT $200
Other Commission 1 $130
Other Commission 2 $250

I would like to split these data with KNIME to get the following output :
VAT Refund. Dinner Expense. Transportation Expense. Commission 1. Commission 2

(with the relevant amounts (1 per row)

Basically, a node that could write the amount in the relevant column based on the sub-category (“Dinner expense”, “VAT refund” etc)

Thanks for your help


Hi @Raph21 , usually when reading a row with multiple columns, it is assumed that all values of all columns belong to the same record of the row.
For example, the first line in your original data “Refund, VAT., $100”, means the Expense Category was Refund, Expense sub-category was VAT and the amount was $100

Now, if you want to “split” this data into what you want, from your original data, what defines a line/record in the new format? Is it the “Refund VAT” line?

I think if you can give an example of the expected output for your original dataset you provided will help

1 Like

Thanks @bruno29a for your help.
Please see attached excel file with input and desired output.

In the new format, the line would be defined by the “sub-category” (for example “VAT” or “Commission 1”)
I hope it is more clear.

Thanks againKNIME Input Output.xlsx|attachment (12.5 KB)

If this is what you’re going for

Then you can use a Pivoting node (pivot on “Expense Sub-Category”, aggregate on “Amount” using List), followed by an Ungroup node to get the different rows.

1 Like

Hi @Raph21 , there was no file attached, can you please re-upload the file?

Hi @Raph21 ,
Please look at this workflow, maybe this will help you.
KNIME.knwf (11.4 KB)

The output I’ve achieved is:

I hope this will be the answer you are looking for.


This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.