article How to use - MaxString & MinString in Member Articles

How to use - MaxString & MinString

robert_mika — Sun, 01 Mar 2015 19:48:19 GMT

Bullet points:

MaxString and MinString belongs to Qlikview String Aggregation function.
They are equivalent to MAX and MIN for integers
They return the highest and lowest string value

What actually a value of a string?

This is the value described by ANSI character standard where 0 ( NULL) is equal to 0 and ÿ to 255.

(For full ANSI character set please attached dosument)

Taking only the standard English alphabet (A-z) ‘A’ will be the MIN and ‘z’ will be the MAX.

Remember:

In ANSI standard A < a and Z < z

(A=65,Z=90,a=97,z=122)

Language specific characters will be not recognized in the place where they are in your alphabet but they will be 'pushed' to the end

(see ANSI table)

MinString and MaxString works on dimensions (columns) only so you cannot use is to evaluate single character or strings .

(MaxString(‘a’,’z’…) is not supported

Examples:

1) MIN & MAX (textbox)

Data	Expression	Results
	MinString(Category) MaxString(Category)

Data

Expression

Results

MinString(Category)

MaxString(Category)

2) MIN and MAX of String per group (Straight table)

Data	Expression	Result
	MinString(Category) MaxString(Category)

Data

Expression

Result

MinString(Category)

MaxString(Category)

3) With condition:

Return Max and Min string based on another column

We are looking for MIN and MAX string value from column Category where Country='Poland'

Data	Expression	Solution	Result
	MinString( if(Country='Poland', Category, null()) ) MaxString( if(Country='Poland', Category, null()) )

Data

Expression

Solution

Result

MinString( if(Country='Poland', Category, null()) )

MaxString( if(Country='Poland', Category, null()) )

4) With LEFT/RIGHT

When used with LEFT the result will be the same as without as the value will be still based on first characters.

Data	Expression	Solution	Result
	=MinString( Right(Country,1)) =MaxString( Right(Country,1))

Data

Expression

Solution

Result

=MinString( Right(Country,1))

=MaxString( Right(Country,1))

5) With MID

Data	Expression	Solution	Result
	=MinString( Mid(Country,2,2)) =MaxString( Mid(Country,2,2))

Data

Expression

Solution

Result

=MinString( Mid(Country,2,2))

=MaxString( Mid(Country,2,2))

6) With Substring

When our data are little scrambled -TextBetween will return string between '.' and end of the row.

Data	Expression	Result
	=MinString(TextBetween(Country,'.','',1)) =MaxString(TextBetween(Country,'.','',1))

Data

Expression

Result

=MinString(TextBetween(Country,'.','',1))

=MaxString(TextBetween(Country,'.','',1))

7) In set analysis

When Criteria of Sales is 2 return Country with highest and lowest string Value

Data	Expression	Result
	=MinString({<Sales={2}>} Country) =MaxString({<Sales={2}>} Country)

Data

Expression

Result

=MinString({<Sales={2}>} Country)

=MaxString({<Sales={2}>} Country)

😎 With Aggr()

For each Country return lowest and highest Category string value.

Data	Expression	Result
	=Aggr(Minstring(Category),Country) =Aggr(Maxstring(Category),Country)

Data

Expression

Result

=Aggr(Minstring(Category),Country)

=Aggr(Maxstring(Category),Country)

If know about other ways of using these functions please let me know.

Re: How to use - MaxString & MinString

Tue, 03 Mar 2015 17:02:53 GMT

Hi all,

Please can you kindly explain why number 4 (With LEFT/RIGHT)

is:

=MinString( Right(Country,1)) India

=MaxString( Right(Country,1)) Peru

Instead of:

=MinString( Right(Country,1)) India

=MaxString( Right(Country,1)) Zambia

Re: How to use - MaxString & MinString

robert_mika — Tue, 03 Mar 2015 19:02:29 GMT

=MaxString( Right(Country,1))

Deliver last value from the string

So we have

last values are:

a,u,d,l,r,a

In ANSI standard we have:

a=97

d=100

l=108

r=114

u=117

so MaxString returns 'u'

and MinString='a'

Re: How to use - MaxString & MinString

Wed, 04 Mar 2015 09:15:58 GMT

Thanks Robert, I understand it now.

Cheers

Re: How to use - MaxString & MinString

antonrus — Tue, 02 Jun 2015 14:58:40 GMT

Hello!

please tell me how to derive value prior maksstring?

For example, I work expression =MaxString(Month)

but the expression does not work =MaxString(Month-1)

Re: How to use - MaxString & MinString

robert_mika — Tue, 02 Jun 2015 15:05:16 GMT

How does your Month data look like?

are the numbers or string characters?

MaxString works on strings only for numbers use Max

Re: How to use - MaxString & MinString

antonrus — Tue, 02 Jun 2015 15:09:20 GMT

Thanks for the quick response!

expression =MaxString (Month)-1 produces a figure we need to get the name of the month.

Re: How to use - MaxString & MinString

Anonymous — Sun, 26 Jun 2016 17:50:24 GMT

Robert,

Thank you so much for the explanation!

RGS!

Daniel.

Re: How to use - MaxString & MinString

Anil_Babu_Samineni — Wed, 13 Jul 2016 11:07:05 GMT

I started my QlikView journey. Simply superb.

thanks

Re: How to use - MaxString & MinString

Anil_Babu_Samineni — Wed, 13 Jul 2016 11:07:59 GMT

So Robert,

Here what is the action running. Can you please explain me

=Replace(MinString({<[Text Boxname] = {'Document\TXout-a-m1-b-t-1'}>} Comment), '|', chr(10))

Re: How to use - MaxString & MinString

joseph_morales — Fri, 15 Jul 2016 23:29:32 GMT

Thank Robert,

Very good explanation of functions!

Regards,

Joseph

Re: How to use - MaxString & MinString

nihhalmca — Wed, 10 May 2017 14:16:44 GMT

Very useful, thanks.

Re: How to use - MaxString & MinString

luiskeyrus — Wed, 29 Nov 2017 15:35:39 GMT

lik nihhalmca‌ very very useful!!!! ty

Re: How to use - MaxString & MinString

amita1621 — Mon, 12 Feb 2018 06:07:26 GMT

I have a Period of format 4q 2017 and i want to go to 3q 2017 . by maxstring(period) i get 4q 2017 but how should i reach to 3q 2017