Filtering rows but take the above row into consideration as well

Hello I have the following data set. which has “1” or some other string/int or a mix in the column 1.

image

The required output is as below.

image

The logic behind the two datasets is that, I need to filter out the column 1 rows, which does not have “1” in it however, need to keep just the row above the filtered out row which will contain “1”

Is there any way I can do this.

I have attached the two datasets in the post for the convenience.

Thank you
Kanishka
output.xlsx (3.4 KB)
input.xlsx (3.5 KB)

3 Likes

This is exactly how I like questions to be posed on the forum.

This is doable in Knime.

If you receive no help within the next 24 hours (which I doubt), I’ll spare some time to solve it for you. But I’m sure other contributors will come and do it fast.

2 Likes

Hi @kanishka271

One way to achieve this is via a Column Expression and new the row access features.

Use expression (column("column1",1) !=1) || column("column1") != 1

It checks whether the previous column is not equals to 1 and checks if the current column is not equal to one. In the Column Expression under the Advanced tab, ensure that you have the checkbox selected, a sufficient windows size defined and the option Value in first (last) row selected.

image

If you subsequently filter for true results with a row filter, you’ll get your desired output.

Hope this helps!

5 Likes

Thank you very much this works

1 Like

Finally a proper case to use Column Expressions :joy: :wink:

1 Like

Haha, these features only increased my love for it :crazy_face:

1 Like

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