topic Re: MaxString() function has an issue in QlikView

MaxString() function has an issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to achieve the max of the Program using Maxstring(). It works for most of the projects. but for this scenario it gives an incorrect output.

Expected Output is B2700.18

But the output showing in the QV chart is B2700.8.

Is there any way I can fix this issue.

Re: MaxString() function has an issue

kamielrajaram — Fri, 15 Sep 2017 20:26:43 GMT

Hi,

Is the field always going to have a number after the point. If so you can try subfield(ID,'.',2) as ID_Order and use this the get the max number.

Kind Regards

Kamiel

Re: MaxString() function has an issue

Clever_Anjos — Fri, 15 Sep 2017 21:05:50 GMT

Excel has the same behaviour , because alphabetically 14,17,18 comes before 2

maybe you can prepare your data appending a '0' before numbers with only one char

Re: MaxString() function has an issue

Clever_Anjos — Fri, 15 Sep 2017 21:10:48 GMT

Maybe using KeepChar(ID,'0123456789.') could sort in a way you need

Re: MaxString() function has an issue

Anonymous — Mon, 18 Sep 2017 16:52:34 GMT

Hi Kamiel,

The subfield works good if we are having a number after the decimal. i.e B2700.2, B2700.3

but I also have the data which has the value it self i.e B2700.

Can you help me on how this works.

Thanks,

Re: MaxString() function has an issue

Anonymous — Mon, 18 Sep 2017 16:57:46 GMT

Hi,

While using this logic,

I would have an issue as I would also have numbers before the decimal as well,

Is there any other way to approach this?

Thanks,

Re: MaxString() function has an issue

Colin-Albert — Mon, 18 Sep 2017 17:03:05 GMT

Try sorting on subfield(ID, '.', 1) & num(subfield(ID, '.', 2) / 1000, '.0000')

You can add this as a column to your chart to test.

Re: MaxString() function has an issue

Colin-Albert — Mon, 18 Sep 2017 17:05:00 GMT

Maxstring is correctly sorting alphabetically.

This will work (I'm unable to edit the previous reply)

subfield(ID, '.', 1) & num(subfield(ID, '.', 2) / 100, '.00')

Re: MaxString() function has an issue

Anonymous — Mon, 18 Sep 2017 17:13:25 GMT

This partially works but modifies the initial data from B2700.18 to B27000.18

Re: MaxString() function has an issue

alis2063 — Mon, 18 Sep 2017 17:38:45 GMT

You can get the desire result based on inbuilt sorting function'Sort'i n QlikView ,You just need to transform data as i.e 2700.18, 2700.18 and put it one variable then use it variable in Sort section .

Hope it will works...

Re: MaxString() function has an issue

Anonymous — Mon, 18 Sep 2017 18:50:09 GMT

I am not sure, on how that works. Can you explain with an example?

Re: MaxString() function has an issue

kamielrajaram — Mon, 18 Sep 2017 19:53:00 GMT

Hi,

Would this help, by making the order value 0

if(isnull(SubField(ID,'.',2)),0,SubField(ID,'.',2))

Re: MaxString() function has an issue

Anonymous — Mon, 18 Sep 2017 20:05:17 GMT

I approached it so far in this way taking all your suggestions into consideration, I am still validating for couple of scenario's though:

SubField(if(Progrm = ID,ID & '.0',ID), '.', -1) as ID_Decimal in the script to identify the decimal value

and in the chart expression,

I broke the Id into 3 pieces to resolve and identify the Max of the ID

MaxString(subfield(ID, '.', 1)) & if(ID_Decimal=0,' ','.') & MaxString( (subfield(ID, '.', 2)))

Re: MaxString() function has an issue

Colin-Albert — Mon, 18 Sep 2017 20:07:45 GMT

You can use this expression on the Sort tab of the chart properties.

Select the sort expression option and enter the subfield expression.

Now you display your data but the sort order is evaluated using the sort expression.