Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
flo2
Contributor III
Contributor III

MaxString / MinString question

Hello,

I ued the MaxString function to group duplicates. ( same id of person but for one text field, one has blank value and the other one no so I want to take the not empty value )

And so I checked the use of this function, it uses the ANSI character and i suppose it does the sum of the AINSI value. 

So if we have an field with empty values and an ' OK ' value it will take the OK one ( cause sum of ANSI value is superior )

But i have a problem when there is a date when i make some tests. for example

flo2_0-1643107003046.png

 

If I have these values, so 4 empty lines and one which has one date, if I use group by and for the second column I use MaxString, according to me it should return the date, but it doesn't, it returns a blank value. 

flo2_1-1643107120058.png

( code is

table1:
LOAD A,
B
FROM
[testMaxString.xlsx]
(ooxml, no labels);

table2:
NoConcatenate
load
A,
MaxString(B) as B
resident table1
group by A;
drop table table1;

 

So i have understood that i sould use the Max Function for numeric values, but still, why MaxString doesn't return the date, the sum of ansi character are superior to blank values no ? 

 

thanks !

 

 

1 Reply
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

According to the doc, MaxString returns the last value in sort order.  Blank will sort after numbers, so I think you can swap in a null for blanks to get what you want. 

MaxString(if(len(Trim(B)) = 0, null(), B)) as B 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com