Finding Max Value of Duration with like IDs and return End Location of field

[mp802377]

I have this table (see below). It tells the starting point of the trip and the end point. The problem is the system marks it as Destination Reached if the customer decided to stay longer than 25 hours. But the customer may have decided to stay a couple of days in one location to visit, but their true destination isn't until further in the trip or the other way around. The true destination is shown for the max number of days a customer is there. For example, the true destination for 57341 is Las Vegas because the max stay was 4 days. But in 57385 the true destination is Vancouver, BC. (e is end of trip)

Is there a way for Qlikview to make this calculation? I had started a concatenate. Something like this:
57341|San Diego|Las Vegas|4|*Las Vegas|Grand Canyon|1|*|Grand Canyon|Telluride|3|*

but with this concatenation, how do I say
left of Max(DurationOfStay}) to previous |  should equal Las Vegas? I had been thinking subfield(Left(. And if the ID is a number how do I do max in a string?

TransactionID	Seg	DesReached	StartLocation	EndLocation	DurationOfStay
57341	1	y	San Diego	Las Vegas	4
57341	2	n	Las Vegas	Grand Canyon	1
57341	3	y	Grand Canyon	Telluride	3
57341	4	e	Telluride	San Diego	-
57350	1	y	San Francisco	Santa Barbara	5
57350	2	e	Santa Barbara	San Francisco	-
57385	1	y	San Francisco	Seattle	4
57385	2	y	Seattle	Vancouver, BC	6
57385	3	e	Vancouver, BC	San Francisco	-
58401	1	y	San Diego	Portland	5
58401	2	e	Portland	San Diego	-

[mp802377 · Accepted solution]

I found a solution and I am posting this here in case anyone has the same issue. I had to do a few things to get it done, but it works.

I first put it all in one string for each transaction with a | separator. One like looked like this: 

57341|y|San Diego|Las Vegas|4|n|Las Vegas|Grand Canyon|1|y|Grand Canyon|Telluride |3|e|Telluride|San Diego|- as entire_trip

then I did another field for just the duration like this:

57341 (4, 1, 3,) as entire_duration

I did change the id field to a text from a number and did a string like below to find the max duration

RANGEMAX(
SUBFIELD(PurgeChar(duration, '()'), ',', 1),
SUBFIELD(PurgeChar(duration, '()'), ',', 2),
SUBFIELD(PurgeChar(duration, '()'), ',', 3)
)

as findMax;

Then I did SET statements.

SET MaxDuration = findMax & '|';
SET findEndLocation = SubField(entire_trip, $(MaxDuration),2);
Set revised_trip_off = SubField($(findEndLocation ),'|',1);

There probably is an easier way to do this, but this works. The main issue I had to fix out of the gate was changing the ID from being viewed as a number when I needed to find the max duration.