How to get number from the text string

[maly0606]

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

[jaibau1993]

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. 

[anushree1]

HI,

I am not sure why keepchar does not work for you

[Vegar]

---

@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

[maly0606]

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) 

[maly0606]

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 . 🙂

[rubenmarin]

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])

[cwolf · Accepted solution]

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

[maly0606]

It works perfect, thanks 🙂