Removing Certain Characters Based on Criteria

Hello,

I have a question regarding Alteryx. I have the following data set below. For some vendors I would like to keep the hyphen in the invoice number (eg. Apple as listed below). However, for vendor ABC I would like to only pull the first 6 characters, and for Amazon I would only like to remove the hyphen in the invoice number.

How would I go about doing this? Would a separate formula be needed for each vendor to produce the desired output?

Appreciate the help!

Vendor	Invoice Number	Desired Output
ABC	123567-2022	123567
ABC	564868-2022	564868
Apple	565484-April2022	565484-April2022
Amazon	648654684-678910	648654684678910

Developer Tools

Regex

Tips and Tricks

Accepted answers

philipmannering

The solution would most likely be an if statement in a Formula Tool. Something like this,

if [Vendor] = 'ABC'
then left([Invoice Number], 6)
elseif [Vendor] = 'Amazon'
then ReplaceChar([Invoice Number], '-', '')
elseif [Vendor] = 'Apple'
then [Invoice Number]
else Null()
endif

All comments

Luke_C

Hi @hannah1818

I leveraged a mapping table since I suspect you have more than these 3 vendors:

I use the join tool to add the rules to the invoice data. The mapping table allows for more vendors to be processed without updating anything other than the input.
Then a formula tool to parse the data using the LEFT and REPLACE functions. If new rules are introduced this will need to be modified

New Workflow4.yxmd

binu_acs

@hannah1818 Another option using substring function

SubString_25_04.yxmd

philipmannering

The solution would most likely be an if statement in a Formula Tool. Something like this,

if [Vendor] = 'ABC'
then left([Invoice Number], 6)
elseif [Vendor] = 'Amazon'
then ReplaceChar([Invoice Number], '-', '')
elseif [Vendor] = 'Apple'
then [Invoice Number]
else Null()
endif

IraWatt

Hey @hannah1818,

I would just use a formula tool like this:

All the best,

Ira

flying008

Hi,@hannah1818

I wish you like the formula: Switch([Vendor],[Invoice Number],"ABC",left([Invoice Number],6) ,"Amazon",Replace([Invoice Number], "-", ""),"Apple",[Invoice Number])

Switch([Vendor],Null(),"ABC",left([Invoice Number],6) ,"Amazon",Replace([Invoice Number], "-", ""),"Apple",[Invoice Number])

录制_2022_04_26_10_42_45_981.gif

hannah1818

Thank you! This worked like a charm

philipmannering

Good stuff

philipmannering

@flying008 You could simplify your switch expression by not having the "Apple" case - and making [Invoice Number] the default,

Switch([Vendor],[Invoice Number],"ABC",left([Invoice Number],6) ,"Amazon",Replace([Invoice Number], "-", ""))

Neater than my formula above.

flying008

Hi,@PhilipMannering

Thank you ! your formula is a good idea !

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167