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

Trimming SKU suffixes

kaitturner
5 - Atom

I have a list of Item numbers that I need broken down to their root SKUs.  Some have suffixes, some have prefixes, others do not.  I need help determining how to trim only those with suffixes within the list.  

 

Example:

Item NumberRoot SKU
100260100260
100267100267
100268100268
100270100270
100270W100270
100276100276
100276P100276
280591280591
280591A280591
PRM2840PRM2840
TCK1524TCK1524
WHU4016WHU4016
WHU4059WHU4059
WHU4072WHU4072
WTI0956WTI0956
WTI0957WTI0957
XE4494XE4494
XM0101XM0101
XM0120XM0120
4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus

If you want just the numbers you can try the text cleanse tool or use this formula:

 

regex_replace([sku],"\u", '')

 

 cheers,

 

mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Ladarthure
14 - Magnetar
14 - Magnetar

Hi @kaitturner,

 

to do what you aksed I would use a find replace tool, supposing you have a database of all "expected" SKU, find a workflow attached with an example with your data!

AdamR_AYX
Alteryx Alumni (Retired)

Here is another formula option without regex 🙂

 

TrimRight([Item Number],"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Adam Riley
https://www.linkedin.com/in/adriley/
MarqueeCrew
20 - Arcturus
20 - Arcturus

@kaitturner ,

 

I'm sorry that I responded while walking.  My friend @AdamR_AYX is making me train for a marathon.  He's out of his mind!  But he's the real-thing here at Alteryx.  He's the person responsible for my RegEx exposure.

 

When Adam gives you a STRING function (as below) it not only is easy to understand, but also it runs faster than a regular expression.  It should run about 3+ times faster (for that function)

 

If you tried to pattern match this, you could use:

REGEX_Replace([Item Number], "(\u*\d*)[a-zA-Z]*$", '$1')

 

So if you're feeling generous and want to try a regular expression as an alternative, here it is.  Otherwise remember that Chaos reigns within.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels