LAE

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

Originally posted by: johnvvu

I'm trying to solve a situation where multiple work orders exist on an account and am having trouble. Here's an example:

Requirement:
A work order containing the code IS504, IS507, or IS505 must have a work order containing the code IZ121 within the last X number of days. The difference between the datecompleted field and the dateentered field on the IZ121 work order will determine if its X days or less.

Looking at this data set, I've sorted the work orders for this one account; I then want to subtract work order 125 from 136 and 123 from 124 to find the difference in days. And for work order 138, we can subtract 125 from it to also find the difference in days. This seems like a challenge requiring a loop. Please advise, thank you!!

account wonumber code dateentered datecompleted
86758 123 IZ121 2015-12-12 2015-12-12
86758 124 IS504 2016-01-10 2016-01-11
86758 125 IZ121 2016-01-15 2016-01-15
86758 136 IS507 2016-02-20 2016-02-21
86758 138 IS505 2016-02-25 2016-02-25



capture.png attached

Capture.PNG

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

Originally posted by: stonysmith

The attached graph should do the task for you.

===
Technical explanation:

It is possible to "persist" (retain) data from one record to the next in BrainScript if you hold the data into local variables.
This Agg here starts by setting the variable Prior_IZ121 to a very old date when it starts working a new account.
It then looks for the code IZ121 and if it finds it, it updates the variable Prior_IZ121 with the newest date that it finds.

Then if it finds one of the other codes, it will write out that record, along with the Prior_IZ121 value that it has been hold and computes the Duration from that value.

Note that we're using a Sort+Agg here instead of the simpler AggEx .. In this case we need to sort on both Account and CompletionDate, but we only want to GroupBy on the Account field.
Attachments:
Duration Between Account Activity.brg

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

Originally posted by: johnvvu

Thank you-I'll give that a try and leave my comments.