I have extracted some invoice data using Power Automate but would like to clean the data using Knime.
The input would have the notes separated by columns as the image below. The result would include all the notes lines under the same row as the output.
I believe staring with, and rule engine should be the first step by wasn’t sure if I would need to run a loop and how to join the rows together.
You can solve this with a GroupBy node. Here, use the Invoice number and SaleOrder as grouping columns.
For the others except the Notes, use the aggregation method First. If you just want to have the notes together in one sentence, you can opt to directly just concatenate it without delimiter.
It you actually want carriage return in one cell, I would recommend using a value delimiter that is a-typical and will never be present in your actual notes to avoid unwanted splits later on, for example <#>.
If you then do
replace($Notes$,"<#>" ,"\n" ) in either a String Manipulation or Column Expression, it will now convert the delimiter to actual carriage returns.
As per the table preview, you’ll now have the notes in accordance with your expected output.
Hope this helps!
Conditional Concatenate.knwf (31.9 KB)
Edit: You are so fast @ArjenEX!
You even bothered to type in some matching data…
At first I couldn’t be bothered but then figured lets not be a complete douche
Thanks, Worked perfectly.
Appreciate the effort Arjen!!!
Great to hear @ADonia! Please mark the post as solution to increase the visibility to fellow KNIME’ers facing a similar issue
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.