Hi Alteryx Community,
I'm looking to create three new columns with data from an existing " Comments" column. In this example i have one column " Comments" as described below, which contains customer and vendor ID and other data. I need to know how to segregate this column to create three sperate columns with headers as follows:
1) Vendor ID ( the number that follows the vendor/vend should be in this column)
2) Customer ID ( The number that follows CUST/CUSTOMER should fall into this column)
3) Other data ( all other data like 2021 Launch etc. should fall into this column)
Comments
VENDOR 90001,CUST-91700
VENDOR 96100 ,CUST-98000
2021 LAUNCH
2021 LAUNCH
VENDOR ID-96000
VEND-96800, CUSTOMER-91600
VEND-96810, CUST-91601
Could someone in the group please help with what formula I need to use or how I can go about achieving the end result.
Your help is much appreciated.
Thank you,
G.
After inputting the data as a single column csv format
Add a record id
Run data through two paths paths, one for Vendor and another for Customer
In one path parse a vendor column out of the input column
In another path parse a customer column out of the input column
Join the two paths on record id resulting in 4 columns:
record id, raw input, vendor, customer
add a 5th column based on raw input but replacing vendor with '' and customer with ''.
This 5th column becomes your comments column.
someone better than me at regex can fix this formula but basically on regex parse if you use:
.*[\s-].*(\d{5}).*cust.*[-](\d*)|id-(\d*)|(\d.*)
You'll get
1) vendor ids - except the one labeled vendor ID-
2) customer ids
3) the missing vendor ID
4) everything else.
You can then use a formula to move the missing vendor id over...
This works on your test set - don't know about your full data. You may want to replace/clean it or wait for the regex superheros to chime in with something better that picks up everything at once...
Thank you for your response.
Thank you for your response , it worked on some data combinations not all. Will keep trying thanks for your help.