String Functions

[#1]

Dear all,

I am stuck at a problem which relates to string functions in Qlikview. The problem I have is with a field that contains Alpha-Numeric characters where both have their importance. A sample of records in the field are as follows:

160TW15.15

160TW15.24

160TW15.29

170E7.05

170E12.6

170E12.24

170E12.49

170E12.55

170E12.63

170E12.67

170JP3.67

170JP3.72

170JP3.77

170JP3.82

170JP3.675

170JP3.684

Here the number and the alpha characters, upto the last alpha represent the name of the Stock and the numbers after the last alpha character represents the weight. The problem I have is that some names are 4 characters long whilst some are 5 so it is a variable. I need a function which can identify the last alpha in each string and separate the two parts. I could do this in the front end or back end! Examples from the above data would be:

1) 160TW represents name and 15.15, 15.24 & 15.29 is its weight

2) 170E is the stock name and 7.05, 12.6 and so on are the weight

Please help!!

[ali_hijazi]

did you try something like this:

Load

mid(your_field,1,if(isnum(mid(your_field,5,1)),4,3)

?

[#3]

yeah i just tried it but its not working! This function is giving me the numbers preceding the alpha characters and not the ones after!

[alexandros17]

Here ii is the expression you need:

Mid('160TW15.29',1,FindOneOf(Lower('160TW15.29'),'abcdefghijklmnopqrstuvwxyz',-1))
Mid('160TW15.29',FindOneOf(Lower('160TW15.29'),'abcdefghijklmnopqrstuvwxyz',-1)+1)

let me know

[ali_hijazi]

then you can use the following

=mid(your_field,if(isnum(mid(your_field,5,1)),5,4))

[#6]

Yup this function is working just fine! Thank you Alessandro!

[#7]

Thanks Ali Hijazi for your help!

[oknotsen]

Do you have any remaining questions related to this topic?

If not, please flag the topic as answered by selecting who gave the (correct) answer . Thank you!

[alexandros17]

Could you please mark the answer?

Tanks

Alexandros