Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chrisgoudy
New Contributor II

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

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)

Tags (2)
1 Solution

Accepted Solutions

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

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

5 Replies

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

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

awhitfield
Esteemed Contributor

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

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

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

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

15-Apr-15 3-11-38 PM.jpg

chrisgoudy
New Contributor II

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

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)

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

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

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

16-Apr-15 12-31-10 PM.jpg

Community Browser