0 Replies Latest reply: Aug 4, 2017 2:58 PM by Randall Lau RSS

    Determining the max value of a field, not globally, between two date fields that are not static

    Randall Lau

      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]

        • I tried a set analysis, but the global filtering caused it to 'work', but not as intended. 


      Thank you for any guidance you are willing to offer!




       

      I have a table with the following dimensions & measures:

      Capture.JPG

       

      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.

      Capture.JPG

       

       

      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)

        •           After I took the time to get this working, I learned that ">=$(=max(FirstRevDateTime))" functions globally
        •           So, while it 'worked', it will only give me the correct value on my max(FirstRevDateTime).

       

      So, I come seeking wisdom.  Is there another way to do this?

        • Is there a way to manipulate that set analysis equation to look for the FirstRevDateTime in a manner that is not global?
        • I think FirstSortedValue or RangeMax or RangeSum are tools I can use to accomplish this, but I haven't been able to identify the logic to get those to work.

       

      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.

      • Should I be using DropTable?
      • Is my use of Resident tables incorrect?

       

       

       

      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;