6 Replies Latest reply: Feb 16, 2018 3:24 AM by Max Maudin RSS

    subfield in long text

    Max Maudin

      Hi all,

       

      I have question about the function subfield. I have a date range in a Long text and number string and I want to know the date range in years. How can I write a formula in the script? E.g.

       

      1246546 LoanA, 1.1% 01.01.2017-01.01.2020 (2354.500)

      8765483 LoanB, 1.5% 31.12.2017-31.12.2022 (5265.522)

       

      So Loan A would give 3 Years and Loan B 5 years. How can I write the subfield formula.

       

      Thanks

        • Re: subfield in long text
          Thiago Justen Teixeira

          Edited

           

          What about this:

           

          Table:

          Load * Inline [

          Text

          '1246546 LoanA, 1.1% 01.01.2017-01.01.2020 (2354.500)'

          '8765483 LoanB, 1.5% 31.12.2017-31.12.2022 (5265.522)'

          ];

           

           

          Final:

          Load

          *,

              Year(SecondDate)-Year(FirstDate)   as YearInterval;

          Load

          Right(SubField(Text,',',1),5) as Loan,

          Date(Replace(Right(SubField(Text,'-',1),10),'.','/')) as FirstDate,

              Date(Replace(Left(SubField(Text,'-',2),10),'.','/')) as SecondDate

          Resident Table;

           

          The result is below:

           

          Capturar.PNG

           

          Cheers

          • Re: subfield in long text
            Thiago Justen Teixeira

            Just to help you understand what I did:

             

            Load

            *,

                Year(SecondDate)-Year(FirstDate)  as YearInterval;

            Load

            Right(SubField(Text,',',1),5) as Loan,

            Date(Replace(Right(SubField(Text,'-',1),10),'.','/')) as FirstDate,

                Date(Replace(Left(SubField(Text,'-',2),10),'.','/')) as SecondDate

            Resident Table;

             

            I'm using 3 arguments in SubField function as its sintax shows:

            SubField(text, delimiter[, field_no ])

            The third one specifies which substring of the parent we are using.



            So in this piece of code what I'm getting is:


            Right(SubField(Text,',',1),5) => I'm getting the first substring where parent's delimiter is a comma (',')

            '1246546 LoanA, 1.1% 01.01.2017-01.01.2020 (2354.500)' => Output is '1246546 LoanA'


            Then, I get only 5 last characters by using right function.

            Right(SubField(Text,',',1),5) => Output is 'LoanA'



            Getting the dates fields:

             

            FirstDate:

            1) Date(Replace(Right(SubField(Text,'-',1),10),'.','/')) => Output is '1246546 LoanA, 1.1% 01.01.2017'


            2) Date(Replace(Right(SubField(Text,'-',1),10),'.','/')) => Output is '01.01.2017'

             

            3) Date(Replace(Right(SubField(Text,'-',1),10),'.','/')) => Output is '01/01/2017' //Replace is required in order to format field as date

            The last step is format field as date.

             

            The same logical sequence is needed to get the SecondDate field.

             

            Then, this preceding load here calculates the year interval between dates.

             

            Load

            *,

                Year(SecondDate)-Year(FirstDate)  as YearInterval;

            • Re: subfield in long text
              Thiago Justen Teixeira

              Hey Max,

               

              Any news (good ones please) about your question? Did I solve it?