Processing time total 1

[ShortyA]

Hi there,
I am looking to extract the processing time on cases. The data is based on events for cases and looks like:

Index,EventDateTime,Event,MinutesUntilNextEventRecord

12345,2002/11/20 16.06.49.91,LOCK,5
12345,2002/11/20 16.11.51.10,UPDATE,1
12345,2002/11/20 16.12.41.12,UNLOCK,60
12345,2002/11/20 17.12.13.85,LOCK,4 etc...

Processing time is defined as the total MinutesUntilNextEventRecord between a LOCK event and the next UNLOCK event. Has anyone got any thoughts on how I could achieve this one ?
Thanks very much,
Alec

 

[SmpTraining]

So what would you expect the answer to be in your example? Steve Phillips, Crystal Consultant

 

[ShortyA]

Hi Steve,
I would expect this to be 6 minutes so far for this example.
Alec

 

[SmpTraining]

OK, you will need this formula in your report for starters - it will extract the minutes as a number

// Formula to extract the last field from a string and convert into a numeric value

// Assign your field to myString
StringVar myString:="12345,2002/11/20 16.06.49.91,LOCK,5";

StringVar myOutput:="";
NumberVar myMins:=0;

// Note: InStrRev is a fairly new function (v8 and above I think).
myOutput := right( myString, len(myString) - InStrRev (myString, ",&quot);

//Covert the result into a numeric
If IsNumeric (myOutput) then
myMins := cdbl (myOutput)
else
0

Steve Phillips, Crystal Consultant

 

[SmpTraining]

This may not be the most elegent way of solving the problem but it should work. I have not been able to test though due to lack of data. Simply conditionally suppress the formula to stop it displaying if not on an UNLOCK line.

Alec, I don't have the data to test this but my updated formula looks like this:

You may notice it outputs a figure for every line

// Formula to extract the last field from a string and convert into a numeric value

// Assign your field to myString
StringVar myString:="12345,2002/11/20 16.06.49.91,LOCK,5";

StringVar myOutput:="";
NumberVar myMins:=0;

// Note: InStrRev is a fairly new function (v8 and above I think).
myOutput := right( myString, len(myString) - InStrRev (myString, ",&quot);

//Covert the result into a numeric
If IsNumeric (myOutput) then
myMins := cDbl (myOutput)
else
0;

NumberVar myTotal:=0;

if InStr(myString, "LOCK&quot >0 then
// Found LOCK
myTotal := myMins
else
if InStr(myString, "UNLOCK&quot >0 then
// Found UNLOCK
myTotal
else
myTotal := myTotal + myMins; Steve Phillips, Crystal Consultant

 

[Naith]

You can do this with much less shenanigans.

Val(StrReverse(Mid(StrReverse({YourLogField}),1,Instr(StrReverse({YourLogField}),',')-1)))

and use a running total on this.

StrReverse is a v8+ addition, so come back if you're on anything earlier, as then neither loops nor StrReverse will work for you.

All the best with your report,

Naith

 

[Naith]

I don't know why I brought loops up. I did read your formula, Steve, and it's patently obvious it's no loop...

 

[SmpTraining]

Like the StrReverse function Naith.

Granted, my solution is quite long winded but I think this helps people new to formulas take things one step at a time. Elegant solutions like yours take some planning and testing.

OK, I admit it, yours is just better! Where were you an hour ago eh? You could have saved me loads of typing! Steve Phillips, Crystal Consultant

 

[Naith]

Sorry.

 

[ShortyA]

Hi folks,
thanks for your replies. Won't both these solutions just effectively convert the MinutesUntilNextEventRecord value and selectively total ie if it is a LOCK then ignore if it is a UNLOCK then add it on? If yes, I am interested in only running a total between LOCK and UNLOCK events only.

Apologies if I have misunderstood your replies.

Alec

 

[Naith]

The StrReverse formula strips the minutes from the rest of the string, so you can manipulate it in a running total or with variables.

If you were to use a running total, you would set a condition to reset the summation when the record had 'unlock' in it.

i.e.

Instr({YourLogField},'UNLOCK') > 0

If you wanted to use variables, you'd have to create a formula that looked like:

WhilePrintingRecords;
NumberVar ShowMe;

If InStr(Next({YourLogField}),'UNLOCK') > 0
Then ShowMe
Else ShowMe := ShowMe + Val(StrReverse(Mid(StrReverse({YourLogField}),1,Instr(StrReverse({YourLogField}),',')-1)))

You'd have to remember to insert a new section below your details section, so you have a Details A and B split, and place another formula which resets ShowMe in B.

WhilePrintingRecords;
NumberVar ShowMe;

If InStr({YourLogField},'UNLOCK') > 0
Then ShowMe := 0;

Steve's last formula is also a good formula to run with if you're going to end up using variables. I haven't got the data to lend itself to testing at the moment, but you might want to take the reset of MyTotal out of his formula and reset it elsewhere as mentioned above, so that the variable accumulates.

Naith