5 Replies Latest reply: Apr 16, 2015 7:31 AM by Robert Mika RSS

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

    chris goudy

      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)
          Alessandro Saccone

          =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)
            Andrew Whitfield

            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)
              chris goudy

              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