Correcting bad data practice: Columns with both date and data

KNIME Analytics Platform
1

I am working with a lot of worksheets that have the same issues.  The columns are like this:

           Col 1         Col  2

Row 1  Meas 1      Meas 1

Row 2  Jan 1         Feb 1

Row 3  155            235

 

I want to create a date column based on row 2 and stack these columns on top of eachother like this:

            Date   Meas 1

Row 1  Jan 1  155

Row 2  Feb 1  235

 

For some reason I am going down a huge rabbit hole for what seems like a common problem.  Is there an easy way to use a row value (in this case the date) to merge columns. 

The best way I have so far is to split based on the measurement name, transpose and split based on date, then add the date as a column and re transpose and try to merge all the columns back together, but this is creating other issues and this seems like I am missing a key node that could do this up front.

2

Hey John,

I would split the Value rows from the rest with RegEx, then transpose both table and join them again by rowID. If you want really every measurement in a extra column, you could do this with the pivot node.

I have attached a workflow which should solve your problem.

Best bengo