Extract information such as amount, date, etc

My desired output:

The Principal sum	$21760000.00
First Installment Due Date	1/11/2018
First Principal and Interest Installment Due Date	1/11/2021
Maturity Date	1/10/2025

I used Regex tool and got the outputs in Field_11 after that I want to form 2 columns as shown above. How can I find November1,2018 using some tool in the Field_11 and convert it into the format DD/MM/YYYY?

I also want to remove all the irrelevant rows.

I have attached my workflow

Automate Loan Amortization.yxmd

Workflow

Datasets

Regex

Custom Tools

Date Time

Data Investigation

Database Connection

Accepted answers

estherb47

Hi @yvkpatel

Took a slightly different approach, as I couldn't reconcile the RegEx tool to parse both types of columns you needed.

So, here goes. A Filter uses Regex_Match to isolate just the rows you want. There are duplicates that will be removed later with the Unique tool.

The Formula tool does the separating. For the "Principal sum" heading, that's a simple if the RegeX Match function from the filter tool is true, then use the "The Principal Sum". Otherwise, grab the text that's in between the quotation marks only.

For the Value column, again, using the Regex match function to determine number versus date. For the date, it's a bit convoluted. Look for the string pattern within the field of a word that begins with a capital letter and ends in "er", followed by a space, a number, a comma, another space, and a 4 digit number. Use DateTimeParse to convert that into a date, and DateTimeFormat to convert it back to a string.

Let me know if this helps!

Cheers,

Esther

Automate Loan Amortization.yxzp

All comments

LordNeilLord

Hey @yvkpatel

Can you include the sample text file?

Without the text file to hand you could try something like: .*means(.*?) to get the month and date, then DateTimeParse(New Field, "%B%d,%Y") to format the date

yvkpatel

PFA - Sample Text file

Sample 1.txt

estherb47

Hi @yvkpatel

Took a slightly different approach, as I couldn't reconcile the RegEx tool to parse both types of columns you needed.

So, here goes. A Filter uses Regex_Match to isolate just the rows you want. There are duplicates that will be removed later with the Unique tool.

Let me know if this helps!

Cheers,

Esther

Automate Loan Amortization.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703