accounts payable numbers with 1 to several bank accounts

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

Have you tried the Unpivot node?

2 Likes

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

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.

image
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:

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

2 Likes

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)

2 Likes

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

Regards

1 Like

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

Regards

1 Like

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