How to look up data in one column and rename a different column

[JTWilliams]

Hello Community

I need to look up part of a description in the "Description" column and when found rename the corresponding data in the Item Number column.  Not sure if this would be a Formula (If/then with embed Contains) or one of the Rename tools.  In the sample data below, the look up data of 304 or 316 varies in location within the description.  Once either is found the corresponding Item Number R320 should be renamed as S304 or S316.

Only been working in Alteryx for a month or two.  

Appreciate your insights.  

Sample Data
Item Number	Description
R326	WC.072MINX49.600 GR33 G90 UNIS
R314	WC.064X49.5625-1010 HB DOM
R320	SC.071X3.773 ST 316 SS
R314	WC.148MIN X 47.00 1015 HB
R326	WC.099MINX52.7500-SS GR55 G90
R300	WC.043MINX48.1250-1008 CR
R500	WC.056X53.1250-1008 HB DOM
R320	SC.071X3.773 PS500/P4100 304SS
R320	SC.071X5.450 PS210  304 SS
R500	WC.0575MINX48.00-1006HB DS
R320	SC.100X3.790 ST 316SSOB
R308	WC.083MINX50.000 SS GR55 ZAM90
R300	WC.052MINX48.0600-1006 CRDS
R308	WC.105MINX52.750 SS GR55 ZAM90
R320	SC.100X5.256 PS200/P1000 316SS
Results
Item Number	Description
R326	WC.072MINX49.600 GR33 G90 UNIS
R314	WC.064X49.5625-1010 HB DOM
S316	SC.071X3.773 ST 316 SS
R314	WC.148MIN X 47.00 1015 HB
R326	WC.099MINX52.7500-SS GR55 G90
R300	WC.043MINX48.1250-1008 CR
R500	WC.056X53.1250-1008 HB DOM
S304	SC.071X3.773 PS500/P4100 304SS
S304	SC.071X5.450 PS210  304 SS
R500	WC.0575MINX48.00-1006HB DS
S316	SC.100X3.790 ST 316SSOB
R308	WC.083MINX50.000 SS GR55 ZAM90
R300	WC.052MINX48.0600-1006 CRDS
R308	WC.105MINX52.750 SS GR55 ZAM90
S316	SC.100X5.256 PS200/P1000 316SS

[jdminton · Accepted solution]

@JTWilliams There are a few ways to do this, but is there anything that can be a bit more specific on the search? I could see the possibility of having 304 or 316 in the description elsewhere. Is it always in the same position within the string, for example?

I've included two methods that can do this based on your sample, but with larger sets of data, you may run into issues with those numbers appearing where it isn't relevant.

 

[JTWilliams]

jdminton

Thank you for the two different approaches.  I am fairly new to Alteryx and not yet familiar with Regex.  Not sure I fully understand the formula or how it works.  The second approach "number appear anywhere..." worked.  Variations of 304 & 316 did show up in the larger data set and needed to adjust the formula to filter out the unwanted data.  

Question - in the "number appear anywhere..." approach, I don't see how the Record ID and Select Tools are being used.  Am I missing a nuance in the flow?

Thank you

[jdminton]

RecordID I use a lot in the event I need to sort back to the original order. In this case, it isn't needed unless you will need to resort at some later point in the workflow. Alteryx does not always maintain your sort order. You can ignore the select tool after the formula. The other was to remove some of the columns parsed. 

The RegEx in the top formula is just removing double spaces (2 or more). If you try to use text to columns and space as the delimiter, it will make a column for each time it encounters a space. By changing where there are two or more spaces, it aligns the columns. On second look, some of the descriptions have no spaces, which means they won't align correctly if you tried to do something different that involved those records. The highlighted below are an example of this. They don't meet the criteria of your original request though so I did not worry about those.

 

[JTWilliams]

Thanks again for the solution.