Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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;
Hey Max,
Any news (good ones please) about your question? Did I solve it?
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?
Max, where I did put "text" you should put "Loan" enter the Subfield function.
Hi Thiago
I've tested it now and it works well, but i took the subfield function without the replace.
Thanks for your help