topic Re: Extract the 1 from the ScriptMonth field (R Month/1/2015) in QlikView

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

Anonymous — Wed, 15 Apr 2015 13:46:48 GMT

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)

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

alexandros17 — Wed, 15 Apr 2015 13:54:07 GMT

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

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

awhitfield — Wed, 15 Apr 2015 14:02:50 GMT

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

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

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

robert_mika — Wed, 15 Apr 2015 14:12:07 GMT

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

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

Anonymous — Thu, 16 Apr 2015 10:49:36 GMT

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

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

robert_mika — Thu, 16 Apr 2015 11:29:41 GMT

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

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

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