LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Hi - very tricky one that we can't crack after hours of trying.

I have a dataset that I need to create a group key for grouped orders. The grouping needs to be by Cust Num, and group together the first failed order to the first Completed Order in the data. So under each Cust Num could be multiple groups, and orders that completed first go will be their own group.

Example dataset attached and below - the "Group Key" field is what I'm trying to create.

So effectively, ,
Script needs to find the first failed order in a list sorted by "Order Placed DateTime" where the "Current Queue Status<> "Completed,
Then find the first "Completed" order for the same Cust Num after this.
All orders between this first failed and the first "Completed" inclusive get a unique Group key.
If the next order is also "Completed", then this order gets it's own unique Group Key as there are no failed orders to group with.
Repeat through all the customer before moving on to the next customer.
If there is no "Completed" order after failed orders before moving on to the next customer, then the failed orders get a unique Group Key.

Order Placed DateTime will always be unique and can be sorted without duplicates. Order No will change for each line.


test.jpg

Thanks,

Dave
Attachments:
Copy of test.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Try the attached.
Attachments:
groupKey.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Perfect, thanks as always