Announcements
cancel
Showing results for
Did you mean:
Creator II

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

1 Solution

Accepted Solutions

*,

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;

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
6 Replies

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

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago

*,

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;

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago

Hey Max,

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator II
Author

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?

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

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator II
Author

Hi Thiago

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