A more comprehensive HELP on Maxstring() / Minstring() than the HELP file

    Hi all,

     

    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

    2017-05-11 12_56_09-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

     

    what would you expect returned from a chart with expression Maxstring(Test)?

     

    According to the HELP, we would expect I.

     

    But...

     

     

    2017-05-11 13_43_34-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

     

    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:

     

    Script code:

    
    
    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:

     

    2017-05-11 13_08_46-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

    and aggregated values:

     

    2017-05-11 13_08_56-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

     

    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?`

     

    2017-05-11 13_08_56-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

     

    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:

    2017-05-11 13_30_15-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

     

    But Qlik does seem to use a plain ASCII sort with Maxstring():

    2017-05-11 13_32_46-QlikView x64 - [E__Users_Stefan_Downloads_Geburtstag_2.qvw_].png

     

    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:

     

    Limitations:

    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)).


    Have fun,

    Stefan


    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