Alteryx Designer Desktop Discussions

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

Formula

SyafiaSyahirah
5 - Atom

Hi everyone,

 

I need help in creating new output column.

 

I want to create new column "month", which taken from the column "name" by using the formula = ToNumber(Right([Name],1)). Input table as below. 

 

Number in the column "name" represents month. 

 

Input table: 

Product CodeCountry CodeNameValue
411140JPValue125
411140JPValue235
411140JPValue345
411140JPValue460
411140JPValue515
411140JPValue612
411140JPValue713
411140JPValue840
411140JPValue935
411140JPValue1033
411140JPValue1141
411140JPValue1258

 

However, the formula only resulted up to number 9 as shown in output table below.

 

Output Table:

Product CodeCountry CodeNameValueMonth
411140JPValue251
411140JPValue352
411140JPValue453
411140JPValue604
411140JPValue155
411140JPValue126
411140JPValue137
411140JPValue408
411140JPValue359
411140JPValue331
411140JPValue411
411140JPValue582

 

Therefore, is there any way that I can filter the month from "name" column, up until month 12.

 

Thank you in advance.

3 REPLIES 3
meeravijayan2011
8 - Asteroid

Hi Syafia, 

I have used regex to extract the number. Attaching the workflow. 

Hope this will help you. 

 

Thanks,

Meera

 

vizAlter
12 - Quasar

Hi @SyafiaSyahirah — You can simply do it by using the formula below:

REGEX_Replace([Name], "[^\d+]", "")

It will just keep the numbers and remove everything else.

And if you want to sort by this new field or use it in a further calculation, then go with a data type as Int64 or Double.

 

vizAlter_0-1600059965485.png

 

 

SyafiaSyahirah
5 - Atom

Hi Meera,

 

It works! Thanks a lot.

Labels