Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Removing Certain Characters Based on Criteria

hannah1818
5 - Atom

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

9 REPLIES 9
Luke_C
17 - Castor

Hi @hannah1818 

 

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

  1. 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. 
  2. 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

Luke_C_0-1650921682775.png

 

 

binuacs
20 - Arcturus

@hannah1818 Another option using substring function

binuacs_0-1650921820188.png

 

PhilipMannering
16 - Nebula
16 - Nebula

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

 

PhilipMannering_0-1650921862412.png

 

IraWatt
17 - Castor
17 - Castor

Hey @hannah1818,

I would just use a formula tool like this:

IraWatt_0-1650921931138.png

All the best,

Ira

flying008
14 - Magnetar

Hi,@hannah1818 

 

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

or

 

 

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

 

 

录制_2022_04_26_10_42_45_981.gif

hannah1818
5 - Atom

Thank you! This worked like a charm 

PhilipMannering
16 - Nebula
16 - Nebula

Good stuff :)

PhilipMannering
16 - Nebula
16 - Nebula

@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
14 - Magnetar

Hi,@PhilipMannering 

 

Thank you ! your formula is a good idea ! 

Labels