topic Re: Taking Max value from and alphanumeric field in QlikView

Taking Max value from and alphanumeric field

Mon, 20 Mar 2017 04:47:32 GMT

I have an alphanumeric field where I need to take the first four characters and then to take the largest number value.

Field example: Desired Output

S876987630987 8

A33027889085F 3

I have tried to use LEFT(Field,4) and then tried a number of things but to no avail.

Any help would be great

Thanks in advance.

Re: Taking Max value from and alphanumeric field

hector_munoz — Mon, 20 Mar 2017 06:19:14 GMT

Hi Rishi,

If you take the string starting at position 2 till the end: Right(<field>, Len(<field>) - 1), you could order the result using a MaxString() function.

Regards,

Re: Taking Max value from and alphanumeric field

bhargav_bhat — Mon, 20 Mar 2017 06:26:04 GMT

Hi Rishi,

Instead of using Left function you can use Mid function to achieve your requirement. Below is the sample code

=Rangemax(mid(FieldName,2,1),mid(Fieldname,3,1),mid(Fieldname,4,1))

Regards,

Bhargav

Re: Taking Max value from and alphanumeric field

Mon, 20 Mar 2017 06:54:14 GMT

Hi Bhargav

With your solution:

602016000088B returns 2 - Incorrect

Q320151843673 returns 3 - correct

4576000000000 returns 7 - correct

9876000000400 returns 8 - Incorrect

Works for some values

Re: Taking Max value from and alphanumeric field

sushil353 — Mon, 20 Mar 2017 06:57:41 GMT

Hi,

Try this:

=RangeMax(

mid(KeepChar(left('FieldName ',4),0123456789),1,1),

mid(KeepChar(left('FieldName ',4),0123456789),2,1),

mid(KeepChar(left('FieldName ',4),0123456789),3,1),

mid(KeepChar(left('FieldName ',4),0123456789),4,1),

)

HTH

sushil

Re: Taking Max value from and alphanumeric field

Mon, 20 Mar 2017 07:10:36 GMT

I ended up with this working solution:

rangemax(mid(field,1,1),mid(field,2,1),mid(field,3,1), mid(field,4,1)) as Test,

Thanks all for your replys