Remove Trailing Portion from a Field using the Formula Tool

[StockMarket]

Hello

I have to create a BATCH MACRO in which I have to process multiple csv files. In this workflow, I am working with csv files inside which I have got a field named [Ticker] and the values present inside it have got either of these 3 at the very end -

"-I"
"-II"
"-III"

Here is the example from actual data -
"ACC-I"
"ACC-II"
"ACC-III"

Now, I want to create a new field named [Ticker_1] which will have the values based on this logic -

If the value present within [Ticker] -
EndsWith([Ticker],"-I") THEN it should have value of "1" inside the new field
EndsWith([Ticker],"-II") THEN it should have value of "2" inside the new field
EndsWith([Ticker],"-III") THEN it should have value of "3" inside the new field

I am not being able to express the above logic in the form of correct FORMULA for creating the New Field [Ticker_1]. Can someone please suggest the right method for creating the new field?

And also I want to create another field by the name of [Ticker_2], which will be ditto same as the original field [Ticker] but it will not have these trailing "-I","-II" or"-III". Basically I want to delete these 3 from the Ticker for creating this new field.

So this [Ticker_2] field, instead of having the values like -
"ACC-I"
"ACC-II"
"ACC-III"
will simply have these values inside it -
"ACC"
"ACC"
"ACC"

Please note that I would prefer to have the formula which will make use of the logic of deleting the trailing "-I","-II","-III" from [Ticker] rather then using the logic of deleting everything after the dash "-" because in some cases the the dash "-" might be present inside the ticker name at any other location as well and I do not want to mess up with that "-" as I simply want to delete the trailing "-I","-II","-III" only and nothing else.

Could someone please suggest the right approach for achieving this aim in the workflow.

I have also attached the Alteryx Worflow Package .yxzp file and the sample data file, so that you can easily open it up on your machine. I have used the latest version of Alteryx for creating this workflow. You may please have a look.

Thanks and Regards

 

 

[danilang · Accepted solution]

Hi @StockMarket 

Here's a modification of your workflow that does what your looking for.

Here's the config of the first formula tool converting filename from string to date w/o select

This converts the values in the field to alteryx standard date format.  If you need to convert the field type to an actual Date format, use this formula in a Multifield tool, which the only tool that can apply a formula and convert the field type in one operation.  

For the Ticker fields I combined them into one formula tool

The first formula uses regex to count the number of I's after the final "-".  The second formula removes the final "-" and following "I", but only if they occur at the end of the string

Dan

[StockMarket]

Thank you @danilang for your help.

All tools are working good.

Best Regards