topic Re: String Functions in QlikView

String Functions

Wed, 15 Apr 2015 05:36:15 GMT

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

Re: String Functions

ali_hijazi — Wed, 15 Apr 2015 07:28:13 GMT

did you try something like this:

Load

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

Re: String Functions

Wed, 15 Apr 2015 07:44:48 GMT

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!

Re: String Functions

alexandros17 — Wed, 15 Apr 2015 07:47:01 GMT

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

Re: String Functions

ali_hijazi — Wed, 15 Apr 2015 07:49:48 GMT

then you can use the following

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

Re: String Functions

Wed, 15 Apr 2015 07:54:23 GMT

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

Re: String Functions

Wed, 15 Apr 2015 07:55:22 GMT

Thanks Ali Hijazi for your help!

Re: String Functions

oknotsen — Wed, 15 Apr 2015 08:57:04 GMT

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!

Re: String Functions

alexandros17 — Wed, 15 Apr 2015 09:01:24 GMT

Could you please mark the answer?

Tanks

Alexandros