- Move Document
- Delete Document and Replies
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to use - MaxString & MinString
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) |
2) MIN and MAX of String per group (Straight table)
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()) )
|
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)) |
5) With MID
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)) |
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)
|
😎 With Aggr()
For each Country return lowest and highest Category string value.
Data | Expression | Result |
---|---|---|
=Aggr(Minstring(Category),Country) =Aggr(Maxstring(Category),Country) |
If know about other ways of using these functions please let me know.
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
=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'
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thanks Robert, I understand it now.
Cheers
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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)
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
How does your Month data look like?
are the numbers or string characters?
MaxString works on strings only for numbers use Max
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thanks for the quick response!
expression =MaxString (Month)-1 produces a figure we need to get the name of the month.
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Robert,
Thank you so much for the explanation!
RGS!
Daniel.
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
I started my QlikView journey. Simply superb.
thanks
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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))
- Move Comment
- Delete Comment
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thank Robert,
Very good explanation of functions!
Regards,
Joseph