A more comprehensive HELP on Maxstring() / Minstring() than the HELP file
the HELP file and the old reference manual are my first places if I want to learn something about Qlik functions. But sometimes I think they are not giving 100% correct or complete answers. For example, the HELP says about Maxstring() function (all below also applies to Minstring() 😞
MaxString() finds string values in the expression or field and returns the last text value in the text sort order.
That's why it's called Maxstring(), right?
Well, if a list box shows
what would you expect returned from a chart with expression Maxstring(Test)?
According to the HELP, we would expect I.
Why? Is this a bug?
I hope I now got your attention for this lengthy document.
If you have spent some time with QlikView, you probably know that the default alignment of values in the list box is giving you some information about the nature of the field values. Here you see that the field values are right aligned, but pure text values should be left aligned, so these values are in fact so called duals.
Ok, so I think now we should start with some
systematic research how Maxstring() really works:
Dual(chr(74-TestID),TestID) as Test;
Dual(10-TestID, TestID) as DualTest,
Text(10-TestID) as TextTest,
If(Odd(TestID), Dual(10-TestID, TestID),Text(10-TestID)) as MixTest ;
If the field contains pure text values, Maxstring() returns the last value in text sort order ascending, as described in the HELP
If the field contains only dual values, it will however return the text representation of the value with the maximum numeric representation, in this sample the max numeric value in field Test is 9, its text representation is 'A'
Same applies if you are using e.g. Qlik dates, created using e.g. Date() function: If your field contains dual date values showing '23/01/2017' and '01/02/2017', Maxstring() will return '01/02/2017' because the numeric value is higher (note that Maxstring() offers here a good method to keep the text representation if you want to filter on max dates), though one would expect '23/01/2017' if you follow the HELP definition (text sort order).
If the field contains mixed values, both dual and pure text values, Maxstring() seems to only see the pure text values. In the sample above, it returns 8, though there exists a higher numeric value of a dual (TestID 9) and also a text representation of a dual with higher sort priority (TestID 1)
Does this also apply for the chart aggregation functions, or do they differ?`