Separating data while still keeping the information from other columns

I have a table where it lists both regular and overtime hours but I am trying to separate the regular hours from the overtime hours while still keeping the information from the other columns.

Original table:

Job Worker Regular Hours Overtime Hours
Job 1 Kim 1 2
Job 2 James 3
Job 3 Mark 4 1

What I want:

Job Worker Regular Hours Overtime Hours
Job 1 Kim 1
Job 1 Kim 2
Job 2 James 3
Job 3 Mark 4
Job 3 Mark 1

Is there anyway to do this?


hi @vivianmpoon,

of course, there is a way with KNIME :slight_smile:
Start with a unpivot to get the rows. Then split the feature name back to columns and recalculate the values.
Check this workflow on the hub. You may drag & drop to import it.

Hope that helps, Tommy


Thanks @tommy! :smiley:

This worked for me, except I have another problem now. For some reason it created 2 extra lines.
For example in this case, it had 2 extra lines for OT 1.5 and one for OT 2.0 even though the value was just 0 for these two. I’m not sure how to get rid of the extra lines. Any advice?


1 Like

hi, I think the Math Formula causes this. Please check this fix:

br, Tommy

Unfortunately, this did not work…

It should be a problem of column exclusion/inclusion setting of any node.
As I don’t have your complete column setup, I’m not able to figure out the solution.

Please check again…

br, Tommy

Hi @vivianmpoon

See if this different approach fixes your question. seperating_data.knwf (25.9 KB)
Schermafdruk van 2021-01-15 20-43-34
gr. Hans

1 Like

I’m wondering if it is because under my current column I actually have way more data


please try the updated hub workflow, this should work.
Otherwise, I really give @HansS approach a try.

@tommy @HansS
Both of your methods worked but I was still getting those duplicated line items so I ended up using another filter to get rid of them.

Thank you both :smile:


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