Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for 
Search instead for 
Did you mean: 
mp802377
Creator
Creator

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?

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 -
Labels (2)
1 Solution

Accepted Solutions
mp802377
Creator
Creator
Author

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.

View solution in original post

1 Reply
mp802377
Creator
Creator
Author

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.