accounts payable numbers with 1 to several bank accounts

[tsv_ml_f21]

Hello again

In a current workflow I have the issue that accounts payable numbers can have one to x bank accounts.
i.e. one accounts payable number with three bank accounts => KNIME creates 3 lines.
i.e. one accounts payable number with two bank accounts => KNIME creates 2 lines.

I would like to have the information of these three bank accounts in one line… How can I get to this point?

Thanks a lot for any input.

Best regards

[iCFO]

Have you tried the Unpivot node?

[tsv_ml_f21]

Thanks for the input, not yet used this node…

But currently I do not know yet how to apply correctly to get reasonable results.

Kreditor = account number
Bankschlüssel = bank-key
Bankkonto = bank account

Any further support?

Thanks and regards

[takbb]

Hi @tsv_ml_f21 ,

It would generally be best to at least give some sample data, or better yet a file, that could be copied/pasted, rather than a screenshot as this allows people to have a quick play as otherwise we have to try to imagine how it would work in our heads, which can lead to suggestions that don’t quite work.

I too was thinking unpivot, but then changed my mind to “pivot” but now having typed in some data, I found that even that became a little problematic.

I eventually settled on grouping (concatenating) by account number, and then unsplitting the cells.

It’s not quite perfect as it doesn’t return the columns in the order you showed. How important is that ordering to you?

Unstack Table.knwf (13.3 KB)

This is actually the reverse of the following problem:

Urgent! How to turn the data format by using unpivoting or other functions?

Hi Everyone, I would like to know that Can I use unpivoting to perform this result? Here is the original data format. I want to combine all order code, amount and cost to three columns respectively. [image] Here is the expected result. [image] Please HELP!!! Many Thanks!!! Knime.xlsx (9.5 KB)

and my solution (apart from the additional column renaming ) is roughly the reverse of the solution given there.

[iCFO]

Good call @takbb - I reversed this in my head. This would indeed be a Pivot node and not an Unpivot.

I went ahead and added how I would use the Pivot node to solve it as a tack on to Takbb’s workflow, so you can see an alternate approach to solving it. I used the Rank and then Pivot node.

Unstack Table.knwf (51.8 KB)

[tsv_ml_f21]

Thanks, good approach. The order is not that important, I can also use this one.

Regards

[tsv_ml_f21]

Thank you; unfortunately I have not installed the latest version and can therefore not open your proposal. I will have to follow-up.

Regards

[iCFO]