topic Re: How to get number from the text string in QlikView

How to get number from the text string

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

Hello ,

I wanna get from the string value for M#

and I did it but I am wondering whether exist any simpler way to get the same result

Below examples of the strings, I have on the left and right side I have text, below you can see only the part of the text which I am interested in.

#%%# RC=Material characteristic RQ=C PCR= M#=207 #%%#
#%%# RC=PIR & SL one off request RQ=C PCR= M#=1 G#=1 #%%#

And in SQL I write

Case when CHARINDEX('M#=',Description)<1 then 'N/A'
else rtrim(upper(SUBSTRING(Description,CHARINDEX('M#=',Description)+3,5
))) end as M#

and in Qlik I add

KeepChar(M#,'0123456789') as M#,

And it works only for values < 99999 maybe do you know more flexible solution ?

Thank you very much for any suggestions

Marcin

Re: How to get number from the text string

jaibau1993 — Thu, 13 Jun 2019 06:47:49 GMT

Hi! I solved it using only Qlik functions (no SQL processing) but I supposed that after 'M#=NUMBERS' goes a blank space. The expression I used is

=SubField(Mid(string, Index(string, 'M#')), ' ', 1)

where "string" is the complete string. It works with the examples you provided. Let me know if it works!

Regards,

Jaime.

Re: How to get number from the text string

anushree1 — Thu, 13 Jun 2019 06:51:43 GMT

HI,

I am not sure why keepchar does not work for you

Re: How to get number from the text string

Vegar — Thu, 13 Jun 2019 07:15:49 GMT

@jaibau1993 wrote:
Hi! I solved it using only Qlik functions (no SQL processing) but I supposed that after 'M#=NUMBERS' goes a blank space. The expression I used is

=SubField(Mid(string, Index(string, 'M#')), ' ', 1)

where "string" is the complete string. It works with the examples you provided. Let me know if it works!
Regards,
Jaime.

Great Jaime!

This expression will return a string including the 'M#=' if you want to exclude the M#= you can adjust it like this;´:

=SubField( Mid(String, Index(String, 'M#') +3 ), ' ', 1)

- Vegar

Re: How to get number from the text string

maly0606 — Thu, 13 Jun 2019 10:26:54 GMT

In sql i cut from string 5 chars and i below case i have 10210 it is wrong because I wanna have 102101

SUBSTRING(Description,CHARINDEX('M#=',Description)+3,5)

#%%# RC=PIR & SL one off request RQ=C PCR= M#=102101 G#=1 #%%#

------- so when I changed 3 argument in substring function from 5 to 6 i get proper resoult for values more than 99999 but for the rest of values for example '3' i got string '3 G#=1' by the keep function result will be 31..

SUBSTRING(Description,CHARINDEX('M#=',Description)+3,6)

Re: How to get number from the text string

maly0606 — Thu, 13 Jun 2019 10:46:45 GMT

In perfect world it will works but unfortunnetly not always I have space beetwen M and next values... sometimes it is somthing looks like space but it it isn't...
For example for below string your function return RC=INV i don't know why.
RC=INV
RQ=C
PCR=R
V#=1
#

But thank you , good to know that exist function like subfield . 🙂

Re: How to get number from the text string

rubenmarin — Thu, 13 Jun 2019 10:59:57 GMT

Hi, that particular case is because there is no M# and it gets 6 characters starting at char zero, you can add a check:

If(Index(String, 'M#')=0, 'N/A', [expression])

Re: How to get number from the text string

cwolf — Thu, 13 Jun 2019 14:05:06 GMT

t: LOAD *, if(isNum(temp),temp,Left(temp,FindOneOf(temp,PurgeChar(temp, '0123456789'))-1)) as M# ; LOAD *, if(index(F1,'M#=')>0,Right(F1, Len(F1)-Index(F1,'M#=')-2),' ') as temp INLINE [ F1 #%%# RC=Material characteristic RQ=C PCR= M#=207 #%%# #%%# RC=Material characteristic M#=999999 RQ=C PCR=345 #%%# #%%# RC=Material characteristic RQ=C PCR=345 M#= #%%# #%%# RC=PIR & SL one off request RQ=C PCR= M#=1 G#=1 #%%# #%%# RC=PIR & SL one off request RQ=C PCR= G#=9 #%%# #%%# RC=Material characteristic RQ=C PCR= M#=211 #%%# RC=Material characteristic RQ=C PCR= M#=987654#%%# #%%# RC=PIR & SL one off request RQ=C PCR= M#=7G#=1 #%%# M#=55588899 12345678 ];

Result:

- Christian

Re: How to get number from the text string

maly0606 — Fri, 14 Jun 2019 08:02:16 GMT

It works perfect, thanks 🙂