Hi @kowisoft,
I’ve had a look and uploaded my proposed solution here: Multiline-Cells v4-Alex – KNIME Community Hub. I kept your workflow and added mine underneath.
What I am doing:
- I split the table so that only the Sales columns go into the loop. The rest I split off and add later again.
- I add an ID column to keep track of which row something belongs to, because in-between I use Ungroup, which may generate multiple rows from one row.
- Then I loop through the sales columns and split each value by the newline character.
- For each line in a cell, I create a new row in the table. The ID of the new rows will be the one from the original row so I can keep track where they come from.
- With Column Rename (Regex) I make sure the new column does not have the “weird” name coming from the Cell Splitter and Ungroup combo, but the original column name.
- I use the Regex Splitter to get the part before and after the equal sign. To handle cases without equal sign, I use an optional non-capturing group
(?:(.*) = )?. - I only keep the split columns and rename them to “Remark” and the other one to the original sales column name.
- I use a Duplicate Row Filter to filter out the second lines generated from the split cells. You can handle this differently, but important is that in the end the number of rows is the same for each iteration. If this is not the case, you can also deal with that using a normal Loop End and a subsequent Pivot. Let me know if you are interested in that.
- In the end we collect the columns, add the ones we previously split off, and concatenate all the remarks.
Let me know if you have any questions.
Kind regards,
Alexander