Extract the 1 from the ScriptMonth field (R Month/1/2015)

[Anonymous]

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)

[alexandros17 · Accepted solution]

=Mid(ScriptMonth, Index(ScriptMonth, '/', 1)+1, Index(ScriptMonth, '/', 2)-Index(ScriptMonth, '/', 1)-1)

[awhitfield]

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

[robert_mika]

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

[Anonymous]

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

[robert_mika]

if(len(TextBetween(Months,'/','/'))=1,TextBetween(Months,'/',')',2)&0&TextBetween(Months,'/','/')

,TextBetween(Months,'/',')',2)

&TextBetween(Months,'/','/')) as MonthNUmber