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.
F.e.
Ordernumber / OrderpositionID
13094223 10.002
13094223 0
13087633 170.001
13087633 0
13094086 10.001
13094086
13094086 20.001
13094086
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)
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.
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
13094086 20001
13094086
13094086
13092686 10004
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.
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;
}
else
{
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
}
else
{
outvalue = column("OrderpositionID")
}
// value to be remembered for next row (if missing)...
prevOrderpositionID=outvalue + 1
// value to be returned for current row...
outvalue
