Looping through rows to add a row that is populated with a value of another row

I am having trouble using the loops in KNIME to loop through my file, add a row after a certain row with ^##Index and populate it with ‘text = (previous rows value)/1000’… I am trying with a rowsplitter but i can’t get it to split on the row value. Then I figure I can use a Add empty row node at the end of the table. After that how do i merge the table back together??

This is all before figuring out how to save a previous rows value to populate the new row…

Any help would be great!
M

Hello @msbarbe2 and welcome to the KNIME forum.

You would not need necessarily to Loop through aiming to populate with previous row. ‘Lag Column’ and ‘Moving Aggregation’ nodes can perform these type of operations.

BR

2 Likes

Thanks! Only thing is I don’t currently have the empty row… How do I add an empty row after a specific (based off the text it has) row?

Hi @msbarbe2 and welcome to the Knime Community.

Would it be possible to share some data and show what the expected results would be for that data?

1 Like

Sure! So I have a jdx file that I am trying to manipulate.

My goals are

  1. Add a row below ##INDEXN and populated it with the text ##VALUE= and INDEXN value / 1000 eg(##VALUE= 2)

  2. Populate ##NAMES= with CASNAMES Text (eg. ##NAMES = Text)

Example:
##Title = Text
##NAMES =
##CASNAMES = Text2
##INDEXN= 2000
##DATA TYPE = Text3

##END=
Then the order repeats again and again

Goal:
##Title = Text
##NAMES = Text2
##CASNAMES = Text2
##INDEXN= 2000
##VALUE = 2
##DATA TYPE = Text3

##END=

1 Like

if I’m getting the right picture:
Does your input data look something like this? in a single column?

image

1 Like

Hi @msbarbe2 , I had the same question as @gonhaddock , so to understand if the “series” repeats.

Also, do all series have these 5 lines?

I noticed also in your example that there is no space between ##INDEXN and the equal:
##INDEXN= 2000

Is that just a typo?

1 Like

Yep sorry no spaces. And yes your table is basically right, there is just a ##END= after each sequence so row 5 would be ##END= then the pattern starts again.

Hi @msbarbe2, thank you for confirming. I put something together for you.

As @gonhaddock mentioned, it is not necessary to use loop to get previous values. Though my workflow uses a loop, it’s for processing the data as groups of these 5 entries.

This is how my workflow looks like:

Input data:
image

Results:
image

Here’s the workflow: Add row with value based on other rows.knwf (37.7 KB)

EDIT: Just a note, for the result of (INDEXN value / 1000) I used an integer column. If you will have decimal (2500 / 1000), it will break. To fix this, just change the type in the Column Expression:
From this (it’s a drop down):


To this:

When switching to double, 2000/1000 will give you 2.0 instead of 2.

Since I can’t see all of your data, wasn’t sure if you had multiples of 1000, in which case may be you want them without decimal, or if you had non-multiples of 1000 also, in which case then you will need the column to be double.

2 Likes

Thank you so much! yeah ill probably use the Number(double). I was wondering so after the datatype there is a string of other rows that aren’t all the same length but each record is always appended by the ##END=. Any recommendation on how to replace the math formula node before the loop with a string find and logs the row number and subtracts that from the title row number or something? Maybe put counter 1 until End is found and then do 2 like nested loops in a javasnippet?

Hi @msbarbe2 , do you mean that ##END= is not only at the end of the file, but rather at the end of each series? Like this?
image

If that’s the case, then you can move the Row Splitter and the Concatenate inside the Loop.

If you can provide a sample data with “special” case, it will help understand

yep! I just added a a java snippet that looped through an labeled the counter correctly. tks for all your help!

1 Like

Actually, if all the series have ##END, then you don’t even need the Row Splitter. This should work:

That was actually my original workflow. And then I added ##END at the end, that’s when I added the Row Splitter, because it was not part of the series. But if all the series has it, then it becomes part of the series, and no need to split. No need for java snippet either, unless your data is different and you have to deal with it differently - it would be nice if you can share what you did.

Just one caveat with it is that join() does not work with empty columns, so I added an extra condition in the Column Expression.

Input:
image

Results:
image

Here’s the updated workflow: Add row with value based on other rows.knwf (33.2 KB)

4 Likes

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