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

Find Replace

kjhanwar
5 - Atom

Hi,

 

Invoice number contains Financial year information like 123/18-19. I want to remove /18-19 and only want 123 as output. There are two question

 

1) If I have all permutation combination of financial year data in invoice number like /18-19, /2018-19, 18-19, 201819 etc. How to remove these financial year numbers from invoice number so that invoice will contain only alpha-bates or number apart from these /18-19, /2018-19, 18-19, 201819 etc.

 

2) if I don't have permutation and combination of financial year information in invoice number, how to remove financial year information in invoice number whenever invoice contains financial year data also.

 

Example

 

InputRequired output
123123
abc/123abc/123
123/2018-19123
124/2018/19124
125/18-19125
abd-18-19abd

 

Basically i want to do the following find replace:-

 

FindReplace
/2018-19 
/2018/19 
/18-19 
-18-19 

 

I have tried with find and replace tools but it was replacing only alpha-bates and not numbers.

 

Please suggest appropriate tool.

 

Thanks in Advance

5 REPLIES 5
JosephSerpis
17 - Castor
17 - Castor

Hi @kjhanwar  I would suggest to use the regex tool for this suggestion to parse the invoice numbers and ignoring the financial year information. I've mocked up an example for simplicity of the the regex code I replaced (-) with / so you can see in the regex code i'm looking for everything before / . Hope this helps.Regex.JPG

AkisM
10 - Fireball

Use the regex tool as JS420 said above, but instead of the expression he used, try using this:

 

[\/\-](\d\d\-|\d{4}).+

 

Choose "replace" as output method. On replacement string, leave it empty to simply delete the unwanted characters.

 

kjhanwar
5 - Atom

Thanks for your quick response.

 

I have used the method suggested by you. This method is not working where my input is STE/18-19/74 (for example), in this case Alteryx is giving me STE as output but i require invoice no as "74" in my output. How to handle these kind of challenges?

 

Some example are mentioned below along with required output and Alteryx output.

 

BeforeOutput coming in AlteryxRequired Output
123/18-19123123
/18-19[Null][Null]
/2018-19[Null][Null]
aaaaaa
/2018/19[Null][Null]
abc/123/18-19abcabc
STE/18-19/74STE74
2018-2019/017201817
18-19/PC/10818108
B2B/000000000666B2B666
BE/03/18-19BE3
AB/1077/18-19AB1077
GCR/18-19/555GCR555
SMP/18-19/468SMP468
FS-188/2018-19FSFS-188

 

Thanks in advance

 

kjhanwar
5 - Atom

Hi,

 

I tried this but it is not giving me desired output. Basically from invoice number i want to remove all special character, alpha-bates and financial year combination so that invoice number contains only number. 

 

With only number in invoice number, I can use concatenate and link it with vendor name and match it with my data.

 

Thanks

nkumarjha
7 - Meteor

Hi @kjhanwar 

 

Use Data Cleansing to Remove Letters and Punctuation use formula tool and use contain and replace formula to achieve the  result. WF attached, 

Labels