Splitting multiple rows by column value to append according to year and month

Hello all!

I want to split the data set based on the company and Material and “append” them next to one universal year and month timeline.
Result: One Timeline for year and month with “Company”, “Material”, “Price1” and “Price2” of every Company and Material mix there is appended next to each other on the “x-axis”.

Please ask, if you have questions; it’s my first time posting.
Here is the example:
structure.xlsx (11.3 KB)

Hi @KeaneEvans

Welcome to the KNIME community. Below is a way of doing it.

It does:

  • Create a data collection for each individual row.
  • Group these per year + month.
  • For each collection, split it again to Company, Material, Price, Price2.
  • Append the result back again the Year, Month.
  • Small clean-up of the column names.

Note1: the same column name like you have in your Excel is not possible so it will always need an additional indicator.
Note2: I took the price values as strings for this illustration purpose, to use the actual amount you need some additional steps to properly convert it.
Note3: I filtered out your dummy lines in the beginning to get a more clean data set through the rest of the nodes.

WF: Splitting multiple rows by column value to append according to year and month.knwf (75.9 KB)

This gets the job done as per your excel sheet. Note that there are probably more efficient methods out there which others for sure can enlighten you with :slight_smile:

4 Likes

I thought this problem was really interesting. Here’s my take:

5 Likes

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