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 |
Solved! Go to Solution.
Hi @hannah1818
I leveraged a mapping table since I suspect you have more than these 3 vendors:
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
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])
Thank you! This worked like a charm
Good stuff :)
@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.