Split data based on categories

[Raph21]

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

Raph

[bruno29a]

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

[Raph21]

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 again (12.5 KB)
Raph

[elsamuel]

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.

[bruno29a]

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

[baharangh68]

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.