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

DataSplit

NiranjanK1
8 - Asteroid

Hi All,

 

I would like to split below data into multiple columns. I have tried it is working for few rows and rest of the rows i got blanks details.

how can archive. please suggest.

 

K20    UE COMM REAL   DVD    0  02/04/22  03/09/21         0.0035000000           9.0035000000

660    TI COMM REAL   DVD    0  02/04/22  03/09/21         0.5000000           0.0023000000

ML0    PI FLUID REAL   DVD    0  02/04/22  03/09/21         9.0035900000           0.0022000000

QQ0    TH GLOBAL REAL REAL 0   DVD    0  02/04/22  03/09/21         5.0015000000           0.0012000000

3ZC    IIFL FIN REAL   DVD    0  02/04/22  03/09/21         4.0035000000           0.00321000000

GR1    HA COMM REAL   DVD    0  02/04/22  03/09/21         0.0003000000           0.007600000

CW3    VA BE REAL   DVD    0  02/04/22  03/09/21         1.0035900000           2.0035000000

 

 

How can i create above data into 8 new Fields

 

Thanks,

NR

 

5 REPLIES 5
SPetrie
12 - Quasar

Regex is your best bet.

Looks like the spacing and some of the widths are constant so an expression like this may work for you. I hard coded DVD since that was the only value I saw in that column, but it can be changed if needed.

(.{3})\s{4}(.+)(DVD)\s{4}(\d)\s{2}(\d{2}\/\d{2}\/\d{2})\s{2}(\d{2}\/\d{2}\/\d{2})\s{9}(\d.\d+)\s{11}(\d.\d+)

SPetrie_0-1682545493704.pngSPetrie_1-1682545508854.png

 

SPetrie
12 - Quasar

Another option that doesnt look quite as messy is replacing double spaces with a new delimiter and then doing a txt to columns on that.

SPetrie_0-1682546055808.png

SPetrie_1-1682546085898.png

I attached a workflow with both the regex and text to columns methods.

 

 

binuacs
20 - Arcturus

@NiranjanK1 Another method

binuacs_0-1682547814621.png

 

NiranjanK1
8 - Asteroid

@SPetrie @binuacs  Thank you for the solution, but Where ever RegExOut2 lenght max 25 RegExOut3 field value coming in RegExOut2, how to fix it for max lenght

binuacs
20 - Arcturus

@NiranjanK1 Can you provide some sample data by mimicking the above scenario? 

Labels