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 |
Solved! Go to 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.
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
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.
Thanks again for the solution.