Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to get number from the text string

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

 

 

 

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: How to get number from the text string

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:

Untitled.png

- Christian

View solution in original post

8 Replies
Highlighted
Partner
Partner

Re: How to get number from the text string

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. 

Highlighted
Specialist II
Specialist II

Re: How to get number from the text string

HI,

I am not sure why keepchar does not work for you

 

Highlighted
Partner
Partner

Re: How to get number from the text string


@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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor II
Contributor II

Re: How to get number from the text string

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) 

Highlighted
Contributor II
Contributor II

Re: How to get number from the text string

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

Highlighted

Re: How to get number from the text string

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

Highlighted
Creator III
Creator III

Re: How to get number from the text string

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:

Untitled.png

- Christian

View solution in original post

Highlighted
Contributor II
Contributor II

Re: How to get number from the text string

It works perfect, thanks 🙂