MaxString / MinString question

flo2 — Tue, 25 Jan 2022 10:42:55 GMT

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

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.

( 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 !

Re: MaxString / MinString question

rwunderlich — Tue, 25 Jan 2022 18:35:01 GMT

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

topic Re: MaxString / MinString question in QlikView

MaxString / MinString question

Re: MaxString / MinString question