replacing missing values with data from the row above AND add 1

Hello there,

im facing a bit of a struggle to get my missing data cleared.
Somehow by exporting my data there are some missing values for position markers. These are always unique for the order number and position.
Ordernumber / OrderpositionID
13094223 10.002
13094223 0
13087633 170.001
13087633 0
13094086 10.001
13094086 20.001
13094086 0
13092686 10.004
13092686 30.001
13094437 10.004
13094437 0
13094437 20.001
13094534 10.001
13094534 20.001
13094534 30.001

The missing Value node would get me in trouble, since f.e. the first blank value would be set with 10001 but this is 10002 instead.
I tried to do something with the rule engine, but i don´t know how to get the value from the Cell above. I read something from a java snipped that could do this, but the given information wouldn´t work for me.
So the logic that im searching for need to provide me the result like this:

IF missing value = take value from above and add 1 to it.

(The “0” is okay, because there will never be an additional position to the 0-Position)

Thanks for any help on this.

Hi @Yannick_Jasper , this should be possible without using the scripting nodes, by utilising lag column as follows:


Attached is a sample workflow.

It is also certainly achievable using java snippet, and (I think) Column Expressions, but I believe the above should give you what you need without scripting.

Adding missing values based on previous row value.knwf (48.7 KB)

1 Like

EDIT: my question for you though is whether you face a situation where there are multiple missing rows together. In that case, do you want it to add one for the first missing row, and then another one to the second and so on…

e.g. if your data were this:
Ordernumber / OrderpositionID
13094086 10001
13094086 20001
13092686 10004

should it become:

Ordernumber / OrderpositionID
13094086 10001
13094086 10002
13094086 20001
13094086 20002
13094086 20003
13092686 10004

If that is the case, the above workflow would need to be adapted

hey @takbb

first of all thanks for the quick response and for the given example the provided workflow is working great.
There might be the possibility that this case could happen, when there is an Order with several subpositions to the same product. I checked my data and the case appears 1 time, for now i would get a missing value.

Is this a quick fix ? :slight_smile:

thanks in advance

Hi @Yannick_Jasper , see new workflow attached

For good measure I have added both a Java Snippet and a Column Expressions equivalent.

Java snippet code:

// Your custom variables:

// retain value of previous OrderPositionID
Integer prevOrderPositionID=0;

// Enter your code here:

if (c_OrderpositionID ==null)
	out_OrderpositionID = prevOrderPositionID;	
	out_OrderpositionID = c_OrderpositionID;

prevOrderPositionID= out_OrderpositionID + 1; // remember latest output + 1 for next row

Column Expressions code:

// prevOrderpositionID is value to be remembered in case it is needed for next row
// it is defined here but NOT initialised.
// For column expressions to "Remember" the value on next row, it MUST NOT BE INITIALISED here! 
var prevOrderpositionID

if (column("OrderpositionID")==null)
    outvalue = prevOrderpositionID == null?0:prevOrderpositionID

    outvalue = column("OrderpositionID")

// value to be remembered for next row (if missing)...
prevOrderpositionID=outvalue + 1

// value to be returned for current row...

See these links for background on how these java snippet and column expressions work

Adding missing values based on previous row value - with contiguous missing values.knwf (71.3 KB)


Hey @takbb

thats more than perfect.
Thanks a lot for these soultions :slight_smile:

1 Like

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