transpose into unknown number of columns

KNIME Analytics Platform
1

Hello all,

 

Here is the (simplified) situation:

  • I have a few columns, one containing a list of result types, one containing result values and one containing well locations.
  • The number of distinct result types is not known until we run the workflow
  • Some well locations will be associated to multiple result types
  • I want to transpose each result type into a new column and populate this column with the result value matching this result type, for this specific well.

 

So, we could have something like this (the tables are not rendered correctly in this forum, so I'm using "/" as a column separator):

Well location/Result type/Result value

A1/X/1

A1/Y/2

A2/Y/3

A1/Z/4

 

And I would like the result to be something like this:

Well location/X/Y/Z

A1/1/2/4

A2/?/3/?

 

So far, this is what I get:

Well location/X/Y/Z

A1/1/?/?/

A1/?/2/?

A2/?/3/?

A1/?/?/4

 

I get to this result by following this workflow:

  1. DB Looping -> Column Filter on my result type column -> GroupBy to get distinct result types -> TableRow To Variable Loop Start
  2. (DB Looping) -> Row Filter based on the variable in TableRow To Variable Loop Start -> Column Rename based on the same variable
  3. (DB Looping) -> Column Filter on well location -> GroupBy to get distinct well locations

2+3 => Joiner (inner join) on well location -> Loop End (Column Append)

 

I'm missing something but I cannot put my finger on it.

Any advice welcome...

 

Cheers,

Fred

2

Hi,

the result you got is nearly there.

follow it up with a groupby node using well location as the group column and all the other columns as the aggregation columns. Choose First as aggregation type for each column and turn off the missing values tick box.

simon.

3

Another way maybe to Pre sort the data. Sort on well location and the result type as secondary sort.

now use a groupby loop start node on the well location.

use rowid node to make result type the rowid.

then transpose table.

Then loop end.

 

simon.

4

Hi Simon, thanks for your replies.

 

I made a mistake in the simplification I made of the result I get. This is more like it:

Well location/X/Well location (Iter #1)/Y/Well location (Iter #2)/Z
A1/1/?/?/?/?
?/?/A1/2/?/?
?/?/A2/3/?/?
?/?/?/?/A1/4

 

This means that the group by is unfortunately not working.

I am looking into your second solution and will let you know.

 

Thanks for your help: whenever I look for a solution on this forum, your name is often in the thread!

 

Cheers,

Fred