Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

Show StopCityState for the Latest StopDateTime

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:

Snipping1.PNG

 

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

Labels (4)
1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

try below

firstsortedvalue(StopCityState,-StopDateTime)

result

Capture.jpg

View solution in original post

3 Replies
dplr-rn
Partner - Master III
Partner - Master III

try below

firstsortedvalue(StopCityState,-StopDateTime)

result

Capture.jpg

JustinDallas
Specialist III
Specialist III
Author

This feels like cheating...

dplr-rn
Partner - Master III
Partner - Master III

he he 🙂