transformar tabla

Hello, I need help: how can I transform the following table, I want all the data that is in different rows to be in a single row

Hi @Zfercho2017

Take the GroupBy node. Skip the first tab . So leave the Group column(s) box empty.
Then in the second tab add all your columns and choose First as aggregation.

gr. Hans

Hello, only with the group by does not solve the need

Ah, I see. That is because of all the "0"s. What if you choose max as aggregation method. And if that doesn’t work out the way you expect, please upload a sample dataset. That makes it easier for the people at the KNIME Forum to create a solution that works for you.

gr. Hans

2 Likes

Hi @Zfercho2017

@HansS method is right. Maybe the “maximum” aggregation function could do the job.
The problem here is that the uninteresting values are not missing values but "0"s in a string type column.

If “maximun” does not work, chose the alphabetic aggregation function which provides the last value in alphabetic order.

Hope this helps.

Best,
Ael

2 Likes

Hi @Zfercho2017 here is another option that builds on the suggestions that have come before.

My take on it was first of all fix the zeroes, and make them “missing”, and then use the GroupBy node:

image

The String Manipulation (Multi Column) can turn zeroes into missing values:

string(
	toEmpty(string($$CURRENTCOLUMN$$)).equals("0")
                || toEmpty(string($$CURRENTCOLUMN$$)).equals("0.0")
	?toNull("")
	:$$CURRENTCOLUMN$$
	)


After that, the GroupBy node can easily group all columns in a single config, using the Pattern tab:

You can either leave Regex ticked and give a pattern of .*, or untick Regex and use a pattern of * as shown here. Make sure you untick “missing”.

Note at the end of this, you have String columns, so you can fix these using a Column Auto Type Cast node
image

or alternatively apply String to Number as needed across the columns that should be numeric (you would have to do Doubles and Integers separately)

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