this needs to be split into columns, that is: each parameter has its own column [methane: 34; cadmium: 20,000....].
i manage to get this done with the pivot node, in that case all other columns are left behind. what is then the best way of joining with the other columns? i don't find a way of getting the right column values together anymore.
either it creates a load of duplicates [and how do i filter these] or an empty table.
I hope, I understand your problem, please correct me if I am wrong. If you want to join the "other" columns with the result of the Pivot node, you can use the Joiner node. Choose the RowID from the pivot table and the other (numeric) column from the original dataset. Note, the latter first needs to be translated into a String using the Number to String node. The resulting table then contains the pivoting table together with all columns from the original table.
i followed your instructions but it does not work yet [or different].
current setup: file -->pivot, file --> number to string, and both pivoting and number to string connected to joiner.
settings: pivoting - pivot column = column i want to split, group [rows] = ID rows, aggregation column = value [sum].
number to string setting: only value column selected [but result same if i select all]. joiner node settings: inner join, left table = row id, right table = value.
result = the pivot does exactly what i want, but only includes the columns listed above. joiner produces empty table. i messed around with different join setttings but this does not lead to a good result either.
i get the result i want when i set the right table to the same column that i used in the pivoting node for group column [row header]. no idea how it worked, but hey, it worked.
perfect. Just to clarify my original post, the Number to String node need to be connected to the File Reader directly rather than to the Pivot node. This makes the Joiner happy to join a RowID with a String-type column.