Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Taking Max value from and alphanumeric field

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.

1 Solution

Accepted Solutions
bhargav_bhat
Creator II
Creator II

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

View solution in original post

5 Replies
hector_munoz
Specialist
Specialist

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,

H

bhargav_bhat
Creator II
Creator II

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

Not applicable
Author

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

sushil353
Master II
Master II

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

Not applicable
Author

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