Copy first and second value in column 4times, then copy value seven and eight 4 times

I have 3 columns with data. In column 3 is data I like to copy 4 times . everytime first two values to 3 to 6 and seventh and eight value to 9 to 12 and so on . See the example. Hopefully this is clear.

Original file
Rowid,col1, col2, col3
Row0,plate1,A1,comp1
Row1,plate1,A2,comp2
Row2,plate1,A3,urt1
Row3,plate1,A4,urt2
Row4,plate1,A5,urt1
Row5,plate1,A6,urt2
Row6,plate2,A1,comp3
Row7,plate2,A2,comp4
Row8,plate2,A3,urt3
Row9,plate2,A4,urt4
Row10,plate2,A5,urt3
Row11,plate2,A6,urt4

Now I like to copy first and second value in col3 and paste this in col3 4times as value 3-6, then copy value seven and eight in col3 and paste in col3 4times as value 9-12

Final file
Rowid,col1, col2, col3
Row0,plate1,A1,comp1
Row1,plate1,A2,comp2
Row2,plate1,A3,comp1
Row3,plate1,A4,comp2
Row4,plate1,A5,comp1
Row5,plate1,A6,comp2
Row6,plate2,A1,comp3
Row7,plate2,A2,comp4
Row8,plate2,A3,comp3
Row9,plate2,A4,comp4
Row10,plate2,A5,comp3
Row11,plate2,A6,comp4

Does anyone have a proposal how to handle this

Hi there,

after a bit of experimenting seems easiest way to go is scripting.

So use Table Column to Variable node and after that apply your logic in some scripting node. I used Column Expressions node with following code.

switch(column("col1")) {
  case "plate1":
    if(rowIndex()%2==1)
    {variable("Row0")}
    else
    {variable("Row1")}
    break;
  case "plate2":
    if(rowIndex()%2==1)
    {variable("Row6")}
    else
    {variable("Row7")}
    break;
  default:
    // code block
}

Br,
Ivan

2 Likes

Hi Ivan,

thanks for your reply. But I have created a too simple example. The actual file I have to update looks different and have much more rows( a couple of thousand). So if I have to adjust the script for all these rows it is not do able.
I have added a part of the file to update and it is about the values in column “Source Plate Code” and “Source Plate Well”. The values in row 6 and 7 of these two columns should be copied to row 8-25 and values of row 26 and 27 to rows 28-45 and values of 54 and 55 to row 56-73 in these columns.

Hopefully you know a solution for this

Thanks,

Ron20190902_test_sql_export_new_wellname_knime.xls (451.5 KB)

Hi,

was afraid it was :smiley:

Anyways you can still use my idea by adding loop and in each iteration modify one group of data. Use Group Loop Start node if you have a identifier otherwise use Chunk Loop Start to define your step. In between Use RowID node so your flow variables always have same names (Row0 and Row1) and simplify your expression like this:

if(rowIndex()%2==1)
{variable("Row0")}
else
{variable("Row1")}

Here is wf pic:

FunnyMod

Br,
Ivan

2 Likes

Hi Ivan,

I have created the workflow and have tried a couple of things with the script, but still did not manage to get it working. At the moment I get the message “No row key column selected generate a new one”.

The values in column 16 of row 4 and 5 should be copied to row 6 until 23. and so on.

this is my workflow until now.

Hopefully you can help,

Thanks

20190905_Copy values number of times.knwf (119.5 KB)

Hi @RonG,

first filter out all rows that don’t need copying to or from. You don’t need them in loop. Later on you can concatenate them. Then define your group column(s) in Group Loop Start node. Those are the columns that will in first loop iteration give you rows 4-23, in second 26-45 and so on… If you don’t have them you should use Chunk Loop Start node and have chunk size 20 as it seems that will give you before mentioned rows.

Try it out. And you don’t have to create new column with RowIDs.

Br,
Ivan

1 Like

Hi Ivan,

thanks for the help sofar. I’m getting there, but still a problem.
I use the chunkloop with 20 rows, then first get the source plate column to update and then the source well column to update. For the first time the loop works well and update the names in the rows 3 to 20.
But the second loop it doesn’t work, because in the script is Row4 and Row5 hard coded and in second loop there is no Row4 and Row 5. This should be now Row24 and Row25. The script is now as you suggested:
if(rowIndex()%2==1)
{variable(“Row4”)}
else
{variable(“Row5”)}

Is there an other way to get the next 20 rows?

// code block

This is a copy of the workflow sofar.

Hi there,

That is way you need RowID node. My comment was that you don’t need to have checked Create new column with RowID values option. See config of this node:

Additionally one Table Column to Variable node is enough cause flow variables are propagated down the flow to all nodes. Also in your case you can have one Column Expressions node with two rules. Click + sign to get additional expression. See picture.

Br,
Ivan

Hi Ivan,

this did the job. Not that understand it completely but will have a closer look at it

thanks for your help,

Ron

1 Like

Hi there @RonG,

glad to hear it is working. Don’t worry, you will figure it out eventually :wink:

Br,
Ivan

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