Finding Max Value of Duration with like IDs and return End Location of field
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?