Alteryx Node Multi Row Formula to Knime

[LukeParkes]

I have attached what the alteryx nodes is programmed to do, and an example which may be easier to follow then my explanation, the first column is coilID, the second is Batch Stage, the third is Split point, the 4th is Apply Ratio ? (the field we are creating)
it groups by coil ID, then the expression below IF split point = Y then we give an answer of “No” to the apply ratio field. Once it achieves this split ratio the remainder columns within that coil ID (with higher batchNo) are given the answer “No”.
I have tried numerous times to achieve this with group by nodes and loops within Knime but i am struggling to get it to completely work, has anyone got a solution ?

[ArjenEX]

Hello @LukeParkes

The access feature in the Column Expression node can handle this. There is an example detailed here:

Recreate Multi-Row result from Alteryx in KNIME

Hi, I’m new to KNIME and are trying to convert an Alteryx workflow. In my input file I have a list of materials and a creation date. The materials can occur several times in the list but have a unique creation date. I want to create a column ‘End Date’ based on Material and the next Creation Date in the list. If the material only occurs once or if it is the last occurrence of the material in the list, the end date should be NULL. [image] So, for material 000000000017550001 in the example …

[takbb]

Whilst Column Expression’s node is the closest match to the Alteryx Row Formula node, unfortunately I don’t actually think it would be the solution in this case.

In the (Alteryx) Row Formula config, it is both creating the “Apply Ratio?” column, and referencing its value in the previous row. Whilst Row Formula can do that, Column Expression cannot (unless there is a very recent change I’ve not seen). Column Expression can be set to retrieve values from previous rows, but not for the Column it is creating. And if it is updating a column rather than replacing it, its “window” on previous rows appears to be the incoming data, and not the changes that it has itself made on this invocation.

NB… I would be very pleased to discover I am wrong on this!

The only node I know of which can set the value of a column based on the value it has set in that same column on a previous row is the Java Snippet.

@LukeParkes , are you familiar with java? The snippet would not be very complex to handle this, but does require some basic syntax knowledge. Arguably it is not much different to knowing the javascript required by Column Expressions node, and I tend to use it in preference. For info on how to allow a java snippet to reference the previous row’s value, see Java snippets have long memories!...

If you are able to upload a small sample data set (with anything confidential removed) hopefully we can assist in finding a solution for you.

[iCFO]

If you don’t need a 1 node solution, this can easily be done with a few base nodes like a short series of Column Expression nodes or Lag with Rule Engine nodes.

[takbb]

@iCFO … I think there is a problem with those solutions though, in that each column expressions or lag column node can only bring the data down one row at a time, so if you have to repeat the data from one row down across the next (undefined) n rows, you would need to call your nodes n times, and the value of n can change from one set of rows to another.

I would be really happy to discover I’ve overlooked a method as this is something I’ve looked at several times in the past, and I haven’t yet found a general solution to this type of problem without using Java Snippet.

It would be great to see this capability added to Column Expressions.

[Edit: I’m sure I heard the distant sound of keyboards and mice heating up as people build some workflows that prove I’m wrong. Challenge? ]

@LukeParkes , Here is a java snippet that I think gives a possible solution to your posted problem, using data I’ve made up.

// custom variables:

String prevSplitPoint="";
String prevApplyRatio="";
String prevCoilId="";

// Enter your code here:

if (!c_Coil_Id.equals(prevCoilId))
{ // coil_Id has changed, so reset prev values
	prevSplitPoint="";
 	prevApplyRatio="";
}
		
if (c_SplitPoint!=null && c_SplitPoint.equals("Y"))
{
	out_ApplyRatio = "No";	
}
else
{
	if (prevSplitPoint.equals("Y") || prevApplyRatio.equals("No"))
	{
		out_ApplyRatio = "No";	
	}
	else
	{
		out_ApplyRatio = "Apply";	
	}
}

/* store copies of current values or next iteration */
if(c_SplitPoint==null){prevSplitPoint="";} else {prevSplitPoint=c_SplitPoint;}
prevApplyRatio= out_ApplyRatio;
prevCoilId=c_Coil_Id;

Forum - java snippet - self referencing column creation.knwf (34.1 KB)