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:
TEST1: LOAD *, Dual(chr(74-TestID),TestID) as Test; LOAD TestID, Dual(10-TestID, TestID) as DualTest, Text(10-TestID) as TextTest, If(Odd(TestID), Dual(10-TestID, TestID),Text(10-TestID)) as MixTest ; LOAD Recno() as TestID AutoGenerate 9; MAXSTRING1: LOAD MaxString(DualTest), MaxString(TextTest), MaxString(MixTest), Maxstring(Test) RESIDENT TEST1;
resulting in a resident TEST1 table:
and aggregated values:
I would conclude:
- 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?`
No, they seem to work in the same way.
What about the sort?
Should be a plain ASCII sort, as explained also in How to use - MaxString & MinString
But, we have learned that Qlik may apply different sort algorithms at different places!
So better test it:
TEST2: LOAD * INLINE [ TextSort A1 A9 A100 ]; LOAD MaxString(TextSort) Resident TEST2;
This is how a non ASCII sort in the list box would look like:
But Qlik does seem to use a plain ASCII sort with Maxstring():
I hope these explanations are a little more comprehensive than the HELP, which I personally find not complete nor 100% correct, as I finally think that the stated limitation therein:
If no text value is found, NULL is returned.
is just not true (well,given the fact that there should be a text representation (the one that is shown in a list box) for all values (numbers, duals, strings)).
P.S. I don't claim that all this is totally new, this has been discussed in few threads, e.g. here.
But as far as I see, there is still a lot of confusion about the Maxstring() / Minstring() functions.
If you like this document, also have a look at Compound Search - demystified