Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

RegEX Challenge

Newbee3
6 - Meteoroid

I am in need again of the alteryx community.

 

Problem: I am having data played into alteryx from a PDF. Consequently I am presented with a string of information from the whole row (see below tab 'start data') which I want to bring into separate columns (see below tab 'goal format'). I tried to go with the "text to columns" function and the delimiters "\s\t" which didn't work out what got me thinking I might have to use RegEx on that one.

 

start data

CH.047'397'801'0 SMIM/BCV 20         342'000.000         101.130CHF         345'864.60         345'864.60
CH.011'559'548'72 EIB 22 EMTN         38'250'000.000             106.900CHF        40'889'250.00          40'889'250.00
CH.013'549'352'36.25 LIONS INVES      21'000'000.000             100.000EUR        21'000'000.00          22'799'070.00
CH.011'862'411'0BARCLAYS 16 FRN       16'560'000.000              98.850EUR        16'369'560.00          17'771'940.20
CH.048'182'357'014.95 BSKT/JPMS 2         1'000'000.000         106.160CHF         1'061'600.00         1'061'600.00
CH.003'965'118'4ENERGIEDIENST HLD         440.000         32.800CHF         14'432.00         14'432.00
CH.000'162'471'4CPH CHEM PAP H N-          3'273.000         79.200CHF         259'221.60         259'221.60
LU.025'263'375'4MUL LY DAX (DR) A         45.000         124.060EUR         5'582.70         6'060.96
GB.00B'421'JZ6'60.5 UK TSY I-L 50         37'400'000.000         257.091GBP         96'152'179.86         123'257'479.36

 

goal format

CH.047'397'801'0SMIM/BCV 20242'000.00201.130CHF855'864.60395'864.60
CH.011'559'548'72 EIB 22 EMTN38'233'000.00106.912CHF110'889'250.00110'889'250.00
CH.013'549'352'36.25 LIONS INVES21'080'000.00180.000EUR26'000'001.0022'809'070.00
CH.011'862'411'0BARCLAYS 16 FRN6'560'000.0098.850EUR16'365'560.0037'771'940.20
CH.048'182'357'014.95 BSKT/JPMS 21'000'080.00122.160CHF1'000'600.001'000'600.00
CH.003'965'118'4ENERGIEDIENST HLD340.0022.800CHF34'432.0034'432.00
CH.000'162'471'4CPH CHEM PAP H N-3'273.007.200CHF25'921.6025'921.60
LU.025'263'375'4MUL LY DAX (DR) A40.00124.060EUR5'582.706'060.96
GB.00B'421'JZ6'60.5 UK TSY I-L 5033'400'000.00997.091GBP96'152'179.86123'257'479.36

 

Looking forward to be hearing from you guys. Thank you already upfront for your help.

Newbee3

8 REPLIES 8
atcodedog05
22 - Nova
22 - Nova

Hi @Newbee3 

 

Here is a workflow for the task.

Regex: Prase mode

 

 

(.{16})\s?(.*)\s([\w\.']*)\s([\w\.']*)\s([\w\.']*)\s([\w\.']*)

 

Output:

atcodedog05_0-1606237398927.png

Config:

atcodedog05_1-1606237432179.png

Hope this helps 🙂 Feel to ask if you have any questions


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

mceleavey
17 - Castor
17 - Castor

Hi @Newbee3 ,

 

I've attached a workflow, I hope this helps.

 

Regex is my friend.

 

mceleavey_0-1606238127534.png

 



Bulien

afv2688
16 - Nebula
16 - Nebula

Hello @Newbee3 ,

 

How does this look like to you? It's not clean but it works!

 

If you have any question about the logic let me know 🙂

 

Regards

PhilipMannering
16 - Nebula
16 - Nebula

I think @atcodedog05 nailed it. See my method attached.

Newbee3
6 - Meteoroid

Gentlemen @PhilipMannering @afv2688 @mceleavey @atcodedog05 

 

thank you very much for your fast response. Apologies for promoting you again, but I think we need to tweak the current solutions slightly to have the final solution fitting the problem.

 

1. Problem

while copying over the data from the different excels to ensure displaying a variety of cases I must have incorporated a mistake. Please find the data again as per below table.

 

2. Problem

It appear that the solutions sent over are lacking two things: a) the currency type (CHF, USD, EUR) is not separated from the number and not put into a separate column. b) some solutions ignore the fact that the first 'item' always must have 13 'digits/characters' - anything afterwards (even if a number) belongs to the name and consequently into the second column (not the case for @atcodedog05 's solution).

 

CH.011'559'548'72 EIB 22 EMTN         18'250'000.000             106.900CHF        40'889'250.00          40'889'250.00
CH.011'862'411'0BARCLAYS 16 FRN       16'330'000.000              98.850EUR        16'369'560.00          17'771'940.20
CH.011'866'825'72.125 BNG 29          11'050'000.000             122.800CHF        14'306'200.00          14'306'200.00
CH.011'954'263'42.00 EIB 35            9'750'000.000             131.000CHF         6'222'500.00           6'222'500.00
CH.012'009'639'81.75 DTEBAHN FIN     822'030'000.000             101.020CHF       183'886'706.00        183'886'706.00
CH.012'295'537'72.25 DBG 21            7'630'000.000             102.010CHF         7'365'122.00           7'365'122.00
CH.012'420'553'22.25 NWB 21           1'825'000.000             102.980CHF        12'177'385.00          12'177'385.00
CH.012'473'990'22.5 CADES 25          20'825'001.000             116.850CHF        24'334'012.50          24'334'012.50
CH.012'506'226'22.5 CRH 21            70'385'004.000             103.530CHF        75'975'490.50          75'975'490.50
CH.012'651'604'32.625 ING BANK 21      9'885'008.000             102.860CHF         9'612'267.00           9'612'267.00

 

thank you again for your support

atcodedog05
22 - Nova
22 - Nova

Hi @Newbee3 

 

Here is the updated workflow.

atcodedog05_0-1606241620641.png

Please take a look and let me know🙂

atcodedog05
22 - Nova
22 - Nova

Happy to help 🙂 @Newbee3 

 

Cheers and Happy Analyzing 😀

 

Feel free to reach out if you face any issues 🙂

BS_THE_ANALYST
13 - Pulsar

Old post but another solution. Nice problem!

BS_THE_ANALYST_0-1676027148481.png

 



Labels