IF cell content begins with 'X' append it to the cell in the row above

KNIME Analytics Platform
#1

Hi, here’s a screenshot of my issue.

zzzscreengrab1646132200001
zzzscreengrab16461322000011006×529 99.9 KB

The spreadsheet author has kindly made this so it will print out nicely, but actually the ‘WITH’ or ‘WITHOUT’ values belong to the row above. To be clear, this is not a merged cell, they have just styled the borders in Excel so that it looks nice.
How would you achieve a node which checks for a cell value starting with with or without and moves it so it gets added it to the end of the cell above?

#2

I guess it’s probably a case of using the Rule Engine, but I’m not sure how to reference the previous or next cell in a rule

#3

I’m not sure it works, but maybe you could add a lagged column (node “Lag Column”) and then use a “Column Expressions” node to combine the two. If you provide a test spreadsheet I can try it myself

#4

Thank you, here’s a test spreadsheet. The first ‘Model’ column has the relevant data highlighted in yellow
car-data-with-without.xlsx (12.0 KB)

#5

here is my workflow car_data_with_without – KNIME Hub. I had to use a Python script, because I couldn’t find a way to lag the column one step back one row. Then I used “Column Expressions” node to add “with / without X” and to replace missing values with blank strings in the “Variant” column where there is in fact no variant (in models “3er / 3-series Compact …”). Hope it solves the problem

2 Likes
#6

Thank you, this looks great. Like you, I couldn’t find a way to get Lag to be negative. This is a great solution with Python :+1:

#7

You can use Moving Aggregation Window size 2 and first as selection shift the lag forward.
br

4 Likes
#8

Hi @DWJames , here’s an alternative without Python script, instead using the Moving Aggregation node (basically using the same idea as suggested by @Daniel_Weikert). This node is one of the most versatile there is in Knime. I’ve used this node for so many different situations, and it’s most definitely useful to do a “reverse” lag column.

Workflow looks like this:
image

Input:

image
image787×794 42.2 KB

Result:

image
image790×792 42 KB

The instructions of the request do not mention what to do with the row where the “with …” and the “without …” were, so I left the empty row there.

Here’s the workflow: If cell begins with specific word move and append to cell above.knwf (24.2 KB)

Note: If you are going to use Python Script, might as well do the whole process there since you are already accessing all the rows :slight_smile:

3 Likes
#9

Thanks, I confirm this works great as well :pray:
So many different ways to get the results with KNIME :grinning:

#10

this is great, thank you again @Daniel_Weikert - You mentioned previously that you might be available for some other data work we have. How can I get in touch with you? :slight_smile:

#11

I am not sure whether this forum allows direct messages. You can find my contact info on my yt channel here when you are logged in yt
br

#12

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