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

# subfield in long text

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

Edited

Table:

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:

*,

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

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:

Cheers

• ###### Re: subfield in long text

*,

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

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.

*,

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

• ###### Re: subfield in long text

Hey Max,

• ###### Re: subfield in long text

Hi Thiago

Sorry for the delay

I've tried it, but Im not sure if we understand each other. These 2 Positions are only a part of many positions

So in the script I have

Loan,

Amount

From [....]

Now the "subfield" must be written unter "Loan" or not?

• ###### Re: subfield in long text

Max, where I did put "text" you should put "Loan" enter the Subfield function.

• ###### Re: subfield in long text

Hi Thiago

I've tested it now and it works well, but i took the subfield function without the replace.