Hi @BSFL89, the thing with using the Java Snippet in this way is that you need to ensure that at the end of the snippet you store away all values that you want to know about on the next invocation (ie. the next record). The snippet remembers nothing from one record to the next unless the values are stored in the “Custom Variables” section.
If you don’t do that there is no way to keep track of what is happening. So you need to have variables that tell you things like who was the last customer I processed, and what was the order number, and what sequence of orders for this customer did we get up to?
You also need to make sure you re-initialise those values at the correct time (e.g. when the customer changes).
If you do those things it can work well, but if you don’t it won’t work the way you want it.
I generally advocate the use of java snippets where:
(1) the processing requires “cumulative” information to be calculated on each row, or
(2) where the algorithm means that use of standard nodes becomes so complicated that writing code is arguably the better solution.
In this case I have not attempted to find a solution using standard nodes, which is something I would normally do. This is primarly because you are asking for help with a java snippet so I’ll assist. It doesn’t mean though that there isn’t a better non-java solution available, such as in the direction that @mlauber71 has pointed, or others may also guide you to.
However it does feel to me that you have “cumulative” calculations in your query (i.e. you are wanting to retain information calculated across multiple previous rows). So in this case I’m willing to accept that the use of the java snippet meets my own “entry-criteria” 
I have put together a snippet to give you the framework. It loosely does what you want but I’m still not 100% certain I understand exactly what that is :-). However please take a look and see how it works. Hopefully if there are any corrections required they should be relatively straightforward but feel free to ask further if it is unclear.
Most importantly, please allow for the fact I may have unintentionally introduced bugs in the code, and it is only very basically tested against my own understanding (the primary reason that I don’t advocate coding in my own case these days unless I have to, as I’m only human!)
java snippet
// Your custom variables:
/* variables defined here carry their value across to next iteration */
int lastCustomerNo=0;
int currentCustomerLineNo=0;
int currentCustomerOrderSeq=0;
boolean isCustOutsideRange=false;
long lastOrderForCustomer=0;
// expression start
public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
/* variables defined here are for this iteration only, and reset on next iteration */
boolean isFirstLineForCustomer=false;
// customer is either returning or not returning (i.e. First Time)
boolean isReturningCustomer=c_customer_type.equals("Returning");
// initialise things based on whether this is a new customer or not
if (c_Customer!=lastCustomerNo)
{
// change of customer means this line is first for this customer
isFirstLineForCustomer=true;
currentCustomerLineNo=1; // reset line no for customer
currentCustomerOrderSeq=1; // used to number the orders
lastOrderForCustomer=0; // reset last order number
}
else
{
// same customer as last time
isFirstLineForCustomer=false;
currentCustomerLineNo+=1; // increment line no for customer
if (c_order_id!=lastOrderForCustomer)
{
// the order id has changed
currentCustomerOrderSeq+=1;
}
}
if(isFirstLineForCustomer)
{
// do some stuff when this is the first line for a customer:
if (isReturningCustomer)
{
// is "First line" but also "Returning"
isCustOutsideRange=true;
out_outsidedaterange = true;
}
else
{
isCustOutsideRange=false;
out_outsidedaterange = false;
}
}
else
{
// do some different stuff:
// set if they are outside date range based on what happened on the first
// line for this customer
out_outsidedaterange = isCustOutsideRange;
}
if (isCustOutsideRange)
{
// OUT OF RANGE: if customer is considered out of range we put "r-" in front of sequence
out_order_sequence_no = "r-" + currentCustomerOrderSeq ;
}
else
{
// IN RANGE: increment order sequence for customer
out_order_sequence_no = "" + currentCustomerOrderSeq;
}
// make a note of what the customer no , last order no is so we can check it on next row
// variables here need to be created in "Your Custom Variables"
lastCustomerNo=c_Customer;
lastOrderForCustomer=c_order_id;
// expression end
java snippet help with returning customers.knwf (25.9 KB)
