Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
HI,
I am not sure why keepchar does not work for you
@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
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)
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])
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
It works perfect, thanks 🙂