Show StopCityState for the Latest StopDateTime

[JustinDallas]

Hello Folks,

So I have a datamodel where I am trying to say the following:

"Show the StopCityState of the latest Stop based on the Tractor Number"

Thus far, I've tried writing this statement for my Straight Table:

If( [StopDateTime] = AGGR( Max( StopDateTime),  TractorNumber ) , StopCityState )

However, this doesn't work at all.

Here is some test data that exemplifies my issue.

Tractors:
LOAD *,
'TRC/' & TractorNumber AS '%stop_asset_join_key'
;
LOAD * Inline 
[
 	TractorNumber, TractorMake
    102, 'Freightliner'
    5001, 'Kensworth'
]
;

Stops:
LOAD *,
StopNumber AS '%stop_number_key', 
Timestamp(
	Timestamp#([StopDateTimeText],'M/DD/YYYY hh:mm:ss tt')
) AS 'StopDateTime'
;
LOAD * Inline
[
	StopNumber, StopTractorNumber, StopDateTimeText, StopCityState
    200, 102, '3/21/2019 10:31:00 AM', 'Spartanburg, SC'
    400, 102, '3/22/2019 11:00:00 AM', 'Rochester, NY'
    600, 5001, '2/19/2019 07:56:00 AM', 'Detroit, MI'
    800, 5001, '2/27/2019 02:11:00 PM', 'Boston, MA'    
]
;
DROP FIELD StopDateTimeText
;

StopsAssetJoinTable:
LOAD StopNumber AS '%stop_number_key', 
	'TRC/' & StopTractorNumber AS '%stop_asset_join_key' 
RESIDENT Stops
;

EXIT Script
;

What I am expecting is the following:

While I know I can perform datamodel work to arrive at the solution, I would rather stick with a chart based solution for now.

Sincerely,

  Justin Dallas

[dplr-rn · Accepted solution]

try below

firstsortedvalue(StopCityState,-StopDateTime)

result

[JustinDallas]

This feels like cheating...

[dplr-rn]

he he 🙂