Would be nice to have something like Qlik's "MaxString" function.
Common use case: Validate that MIN(MyVal) = MAX(MyVal) for a given grouping.
The goal is to to confirm alignment between two columns.
For example, confirm for a given bus trip that the bus driver on a given route is the same throughout the Trip.
I would do something like this in SQL:
SELECT
trip_id
FROM
trip
GROUP BY
trip_id
HAVING
MIN(driver_id) <> MAX(driver_id)
It is a somewhat common use-case.
I tried using the "Any" function, but that doesn't work for this use case.
But if there is an aggregate function I'm not aware of then happy to use that.
I used the example from the Qlik's web-help and here is a workflow that does the same. Technically, it should probably be an aggregation mode in the aggregation actions. Am I getting it right?
maxstring.morph (4.5 KB)
Yes this is effectively accomplishing the same result as the MaxString function, albeit using the Sort + Enum Rows + Filter to accomplish this.
As for where to fit this into EasyMorph?
Yes, the Aggregate action is where I would expect to find it.
You can either modify the existing MAX function to include Strings (like what SQL does) or add a separate MaxString function (which may be safer to avoid impacts on existing projects, but may add clutter).
Having a single MAX function is a bit more simple (like what SQL does).
The reason Qlik separates it out into separate functions is due to their "dual" datatype, which could potentially give two different results for the same column:
MaxString returns the string associated with the maximum numeric value, or if there is no numeric value MaxString returns the string's collated maximum.
