Qlik Community

QlikView Documents

Documents for QlikView related information.

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

MVP
MVP

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

Comments
roharoha
Valued Contributor III

Hi Stefan, thanks for answering my question in a detailed way!

In my opinion, it would be consequent having a


MaxStringString() and a MaxStringDual() function ...

Or
Valued Contributor II

The fact that the text was justified to the right, rather than to the left, was a dead giveaway (and a feature of QlikView I find quite annoying for displaying number/string dual fields - if I'm displaying text, why would I want it justified like a number just because there's an underlying numeric value?)

I appreciate the write-up - would be good if the dual-related issues were added to the official documentation, I think.

qlikview_yj
Contributor

thanks swuehl

Version history
Revision #:
1 of 1
Last update:
‎05-11-2017 07:41 AM
Updated by: