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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Text to columns for multiple fields

Highlighted
Alteryx Partner

Hi All,

 

I've few date columns (say 6-7), I would like to do text to columns so that I can extract dd and mm and yyyy into 3 new columns.

If we have a single data field, I would use Text-to-column tool and get it, but how do we do the same when we have more date fields.

 

  • I would not like to use 6-7 text-to-columns to complete my job.
  • I do not want to use substring since my date fields will not be in same format. (like dd-mm-yyyy, dd-m-yyyy, d-mm-yyyy)

 

Attached is the sample data for reference.

 

Appreciate for the response.

Pulsar

I would use the Multi Field Formula with [_Currentflied_].

 

The first thing is to convert all your dates to a date type, then you can extract day, month and year for each field.

 

See attached workflow.

 

datetime currentfield.png

I would do the following:

 

2018-11-22_18-19-25.png

- Use a MultiField formula to sort the inputs out so all in Alteryx Date Format:

REGEX_REPLACE(
   REGEX_REPLACE([_CurrentField_],"\b(\d)\b","0$1"),
   "(\d{2})-(\d{2})-(\d{4})",
   "$3-$2-$1")

Will work for the list of formats you supplied (first pad with 0 then flip Year and Day if needed, finally convert type in the MultiField tool)

- Add a record ID

- Transpose so each field is a row

- Use a Regex to split the fields to D, M, Y

- Transpose again so D, M, Y are rows too

- Make a new column name combining the original field and D,M,Y field names

- Crosstab to build output

 

 

Labels