Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. Long time listener, second time caller.
My question:
- I am trying to find the maximum of the field NomEG between: [FirstRevDateTime] <= [Date Time] <= [BodyUpStartTime]
Thank you for any guidance you are willing to offer!
I have a table with the following dimensions & measures:
I am trying identify the max value of a field.
MAX(NomEG)
However, I want that number for each HaulID. The table does that.
I want the max NomEG from the last five minutes of a haul AND after the first reverse. Another way to say this is a truck will drive around an unknown amount of time. I only care about the last portion of the haul, the last five minutes. Then, I want to review what is happening once the driver ! starts backing up.
The problem I have run into is the first reverse in the last five minutes moves. To locate where that was, I used this FirstSortedValue to find FirstRevDateTime.
That worked fine for identifying my 'FirstRevDateTime'. However, using that as a range definition point continues to allude me.
- I am trying to find the maximum of the field NomEG between: [FirstRevDateTime] <= [Date Time] <= [BodyUpStartTime]
- It seemed like an easy next step...
I tried using Set Analysis:
aggr(min({$<[Date Time] = {">=$(=max(FirstRevDateTime))"},ReverseRequest={1},ForwardRequest={0}>}NomEG),HaulID)
So, I come seeking wisdom. Is there another way to do this?
Any guidance would be greatly appreciated. I have pasted the main portion of my script below.
On another note...I am a noob at this. I am aware I am likely not using Table to Table relations correctly. If you have any insights on ways to improve my table to table calculations, I would welcome inputs.
Thank you for any guidance you are willing to offer!
NanoStickCalculations:
Load
[Truck Date Time]
,[Dump_Body_Indication] as DBI
,Timestamp([Date Time]) as DT
// Number of Hauls Counter: Body Down to Body Up From 1 to 0, 0-1
,If((Dump_Body_Indication - Previous(Dump_Body_Indication) = -1)
OR (TruckNumber - previous(TruckNumber)) <> 0
,RangeSum( 1, Peek( HaulID ))
,Peek( HaulID )
)
as HaulID // HaulID appears to be working correctly. Confirmed both DBI and TN equations work
//(((( Add total haul time )))))
// Single Haul Time - Confirmed Demo
,If(Dump_Body_Indication - Previous(Dump_Body_Indication) <> -1
,RangeSum( 1, Peek( SecondsOfHaul ))
,0
) as SecondsOfHaul
// Start of Reverse
,if(ReverseRequest - Previous(ReverseRequest) = 1
,1
,0
) as ReverseBinary
// Start of Forward
,if(ForwardRequest - Previous(ForwardRequest) = 1
,1
,0
) as ForwardBinary
// Rev Time
,If(ReverseRequest = 1
,RangeSum( 1, Peek( SecondsOfReverse ))
,0
) as SecondsOfReverse
// Number of Rev Counter: Start of Rev
,if(ReverseRequest - Previous(ReverseRequest) = 1,
RangeSum( 1, Peek( ReverseID ))
,Peek( ReverseID )
) as ReverseID
// Number of Fwd Counter: Start of Fwd
,if(ForwardRequest - Previous(ForwardRequest) = 1,
RangeSum( 1, Peek( ForwardID ))
,Peek( ForwardID )
) as ForwardID
// Fwd Time
,If(ForwardRequest = 1
,RangeSum( 1, Peek( SecondsOfForward ))
,0
) as SecondsOfForward
Resident NanoStick
Order by [Truck Date Time] asc; // Order by appears to be working.
NanoStickCalculations2:
Load
[Truck Date Time]
,ReverseBinary as RB
,ForwardBinary as FB
,Timestamp(DT) as DT2
,HaulID as HID
,ReverseID as RID
,ForwardID as FID
,if(DBI - Previous(DBI) = 1,
[DT]
, peek(BodyUpStartTime)
) as BodyUpStartTime
,if(DBI - Previous(DBI) = 1,
Date(([DT])-(5/(24*60)))
, peek(FiveMinStartTime)
) as FiveMinStartTime
Resident NanoStickCalculations
Order by [Truck Date Time] desc;
NanoStickCalculations3:
Load
[Truck Date Time]
,HID as HID2
,RB as RB2
,FB as FB2
,Timestamp(DT2)
,RID as RID2
,FID as FID2
,if(RB = 0
, 0
,if([DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime
,1
,0
)
) as RevFiveMinBinary
,if(FB = 1 AND [DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime
,1
,0
) as FwdFiveMinBinary
,if(RB = 1 AND [DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime
,[DT2]
,0
) as RevFiveMinTimes
,if(FB = 1 AND [DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime
,[DT2]
,0
) as FwdFiveMinTimes
Resident NanoStickCalculations2
Order by [Truck Date Time] asc;
NanoStickCalculations4:
Load
HID2
, Timestamp(FirstSortedValue(DISTINCT DT3,RevFiveMinBinary=1)) as FirstRevDateTime
, FirstSortedValue(DISTINCT RID2 ,RevFiveMinBinary=1) as MinReverseID
, FirstSortedValue(DISTINCT FID2 ,RevFiveMinBinary=1) as MinForwardID
Resident NanoStickCalculations3 Group By HID2;