Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is there a function to extract the numbers 1-12 from the ScriptMonth field?
(R Month/1/2014)
(R Month/2/2014)
(R Month/3/2014)
(R Month/4/2014)
(R Month/5/2014)
(R Month/6/2014)
(R Month/7/2014)
(R Month/8/2014)
(R Month/9/2014)
(R Month/10/2014)
(R Month/11/2014)
(R Month/12/2014)
=Mid(ScriptMonth, Index(ScriptMonth, '/', 1)+1, Index(ScriptMonth, '/', 2)-Index(ScriptMonth, '/', 1)-1)
=Mid(ScriptMonth, Index(ScriptMonth, '/', 1)+1, Index(ScriptMonth, '/', 2)-Index(ScriptMonth, '/', 1)-1)
Hi Christopher,
you can use the 'subfield' function, e.g.
160210:
Load * Inline
[
Stuff
(R Month/1/2014)
(R Month/2/2014)
(R Month/3/2014)
(R Month/4/2014)
(R Month/5/2014)
(R Month/6/2014)
(R Month/7/2014)
(R Month/8/2014)
(R Month/9/2014)
(R Month/10/2014)
(R Month/11/2014)
(R Month/12/2014)
];
Load
Subfield(Stuff, '/',2) as DateNum
Resident 160210
From the Reference Guide:
subfield(s, 'delimiter' [ , index ] )
In its three-parameter version, this script function returns a given substring from a larger string s with delimiter
'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is
omitted when subfield is used in a field expression in a load statement, the subfield function will cause the
load statement to automatically generate one full record of input data for each substring that can be found in
s.
In its two-parameter version, the subfield function generates one record for each substring that can be taken
from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load
subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'
subfield(S, ';' ,1) returns NULL if S is an empty string
subfield(S, ';' ,1) returns an empty string if S is ';'
Regards
Andy
t1:
LOAD * Inline
[ Months
(R Month/1/2014)
(R Month/2/2014)
(R Month/3/2014)
(R Month/4/2014)
(R Month/5/2014)
(R Month/6/2014)
(R Month/7/2014)
(R Month/8/2014)
(R Month/9/2014)
(R Month/10/2014)
(R Month/11/2014)
(R Month/12/2014)
]
;
load
Months,
TextBetween(Months,'/','/') as MonthNUmber
Resident t1;
drop Table t1
Thanks everyone. All helpful information!
Can I also transform the following?
(R Month/1/2014) to 201401
(R Month/2/2014) to 201402
(R Month/3/2014) to 201403
(R Month/4/2014) to 201404
(R Month/5/2014) to 201405
(R Month/6/2014) to 201406
(R Month/7/2014) to 201407
(R Month/8/2014) to 201408
(R Month/9/2014) to 201409
(R Month/10/2014) to 201410
(R Month/11/2014) to 201411
(R Month/12/2014 to 201412
if(len(TextBetween(Months,'/','/'))=1,TextBetween(Months,'/',')',2)&0&TextBetween(Months,'/','/')
,TextBetween(Months,'/',')',2)
&TextBetween(Months,'/','/')) as MonthNUmber