Read columns dynamically and merge them row information

[drabbit]

Hi there,

I have some problems with the following project, but I am sure you can help me and show me the right way.

We will get an xlsx file and it will look like in the screenshot, but what we don’t know is how many columns we will receive. In this example we have only 4 topics, but in the next xlsx there can be less or more.

Here how the solution should look like:

My main issue is that i don’t now how many columns will received and how they are named, because the value of the interest can change. My idea was the following:

1. Use “Regex Split” to separate the interests
2. Search with the String of the interest if it is included in an column name if there is a match merge Interest and Topic values

I will work on this topic and will do some updates if I will have any progress…

How can i eliminate the | of the Column names? I used "Topic | " in the Column Rename as search string…

thanks
Daniel

[AlexanderFillbrunn]

Hi @drabbit,
Welcome back! I see you were last active here 3 years ago and I am happy to see you are still using KNIME! For your problem, you can use the Column Aggregator node. In the “Columns” tab, choose Wildcard/Regex Selection and then as pattern enter “Topic*” (without quotes). You should see the right columns selected. When new columns come in that match the pattern, they are included automatically. In the “Options” tab, choose “Concatenate” as aggregation function and you should almost get what you need (change the value delimiter at the bottom to semicolon). To prepend the interest, you can use a String Manipulation node with the join() function.
Kind regards,
Alexander

[drabbit]

Hi @AlexanderFillbrunn,
yes I am back, here in the forum, but I was always using KNIME in the last 4 years

Thank you for your Answer I will try it
br Daniel

[drabbit]

I had a look at your answer, the problem is, that i will concatenate all Topics in one row missing the interests.

Solution should look like:
Food: Burger | Car: Ford; BWM
Interests: Topic | Food | Interests: Topic | Car

[AlexanderFillbrunn]

Hi @drabbit,
Oh yeah, I misunderstood. Please find attached a workflow that does what you need. It is a bit more complex than the before solution, but should generalize to more topics.
Alexander

Summaries.knwf (18.7 KB)

[drabbit]

Thank you so much - now I need to have a deeper look at your flow to understand erverything - tank you I for your help and supporting me getting better in KNIME