community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

RegEx Question

Hi I wanted to asked for some help on how to use Regular Expression to Describe these two cell so I can use Reg Ex to parse the data. It's still relatively new to me after going through some crush course available in the forum here. 

 

The first item is "Beginning G/L Balance, 100 -  Cash" 0.00

 

And the second item is "07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond  - C" "04C" 0.01 0.00 0.01" 

 

I have a text file full of these two items above that I am struggling to use RegEx to parse the data. 

 

What i had tried for the first one was" (\".*),\s+(\d{3}\s+\-\s.*\")+(\(*\d+\.\d{2}\)*)' but apparently it didn't work....

 

Thanks for your help in advance. 

Alteryx Certified Partner
Alteryx Certified Partner
Can you please post data as:

Data vs desired results?

Thanks,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

@MarqueeCrewThanks for the response. See data below. I wanted to separate them by each column (i.e. Book Date, Trans # and then all the way to Balance PC") So I needed help with the Reg Ex here.

Alteryx Certified Partner
Alteryx Certified Partner
Sorry, I'm answering via my phone. I won't see xlsx data until tomorrow. If you have text to show, I can try now.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

@MarqueeCrew Hi Mark- the text file is quite large so I cannot type all here. but it looks like this.

 

"General Ledger to To Transaction Reconciliation (All Accounts)"

"100 - Cash"

"07/01/2018 through 07/20/2018"
"Book Date" "Trans #" "Trans. Code" "Short Name" "SEC_NAME" "Portfolio" "Debit PC" "Credit PC" "Balance PC"

"Beginning G/L Balance, 100 - Cash" 0.00
07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond - C" "04C" 0.01 0.00 0.01
07/02/2018 20180628011906 "Buy" "89175VAA1" "TPMT 2018-2 A1 3.25% 03/25/2058" "04" 0.00 866,969.78 (866,969.77)
07/02/2018 20180628011946 "Buy" "15135BAH4" "CENTENE CORP 6.125% 02/15/2024" "04" 0.00 933,778.85 (1,800,748.62)

Alteryx Certified Partner
Alteryx Certified Partner
Sorry, I forgot to look for this today. When you get the text, what are you expected to do with it?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

@MarqueeCrew I wanted to parse the data from this text format below 

 

"General Ledger to To Transaction Reconciliation (All Accounts)"

"100 - Cash"

"07/01/2018 through 07/20/2018"
"Book Date" "Trans #" "Trans. Code" "Short Name" "SEC_NAME" "Portfolio" "Debit PC" "Credit PC" "Balance PC"

"Beginning G/L Balance, 100 - Cash" 0.00
07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond - C" "04C" 0.01 0.00 0.01
07/02/2018 20180628011906 "Buy" "89175VAA1" "TPMT 2018-2 A1 3.25% 03/25/2058" "04" 0.00 866,969.78 (866,969.77)
07/02/2018 20180628011946 "Buy" "15135BAH4" "CENTENE CORP 6.125% 02/15/2024" "04" 0.00 933,778.85 (1,800,748.62)

 

to include only the following information and i wanted to have the data in individual column (i.e. "Book Date" with the dates in one column, and that applies to all columns to "Balance PC") I wanted to do that so I can play around with the data, right now i am stuck at the data parsing phrase. Thank you!

Header "Book Date" "Trans #" "Trans. Code" "Short Name" "SEC_NAME" "Portfolio" "Debit PC" "Credit PC" "Balance PC"

Description"Beginning G/L Balance, 100 - Cash" 0.00
Data 07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond - C" "04C" 0.01 0.00 0.01
07/02/2018 20180628011906 "Buy" "89175VAA1" "TPMT 2018-2 A1 3.25% 03/25/2058" "04" 0.00 866,969.78 (866,969.77)
07/02/2018 20180628011946 "Buy" "15135BAH4" "CENTENE CORP 6.125% 02/15/2024" "04" 0.00 933,778.85 (1,800,748.62)

 

 

Alteryx Certified Partner
Alteryx Certified Partner
Please email me your availability for Monday or Tuesday at 4:00 Eastern for a WebEx. I will send to your email.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

@MarqueeCrew monday @4PM ET works for me. email address : steven.li@pwc.com

 

Thanks! 

Meteor

@stevenlsj789

is this what you're looking for?

 

Screen Shot 2018-08-03 at 6.40.01 PM.png

 

it's just my opinion but i'd recommend against writing a complex regex expression and, instead, go with a sequence of tools to do the work. there's significant upside to going this route--perhaps none more important than saving your future self from spending hours supporting/maintaining said work in the event you share it with others.

Labels