subfield in long text

[madmax88]

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

[Thiago_Justen_]

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:

Cheers

[Thiago_Justen_ · Accepted solution]

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;

[Thiago_Justen_]

Hey Max,

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

[madmax88]

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

Load

Loan,

Amount

From [....]

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

[Thiago_Justen_]

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

[madmax88]

Hi Thiago

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

Thanks for your help