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

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