Hello,
I'm pretty new to Alteryx and am trying to process a dataset that includes a concatenated string column comprised of user-entered items and quantities. I suspect RegEx would be the way to handle this, but don't have enough experience with it and was hoping someone could help out. I have attached a very simplified example of what the incoming data would look like and the desired output.
- Input data is from a user-entered list of items (I have used fruit in the example to simplify matters: apples, bananas, grapes, pears and oranges).
- There may be spaces between the items or not ("1apple 1 banana", "1 apple1banana", "1apple 1banana", etc.)
- The items may be entered as singular or plural ("apple" or "apples") but this does not affect the quantity recorded.
- If the item in the list is preceded by an integer, that integer represents the quantity. If there is no integer, the quantity is 1. If the item is not present in the list, it is recorded as either null or 0 (doesn't matter which, easily changed later, 0 is better)
- The items of interest in the list are contained in a separate text input
- Anything not processed is collected in another column to later be checked for typos, etc.
Basically I want to parse the input data for the items in the list and store the quantity of each item in a separate column, with the remaining string in a separate column.
Any help would be greatly appreciated!
Thank you 🙂
In the example attached:
Input data:
| Input |
| 1BANANAAPPLE 2ORANGES1GRAPES 1PEAR |
| 1APPLESPEARS 2BANANA ORANGE |
| 1GRAPE1ORANGES PEAR1APPLESPEARS |
Item list:
| Items of Interest |
| Apple |
| Banana |
| Grape |
Desired output:
| Input | Apple | Banana | Grape | Other |
| 1BANANAAPPLE 2ORANGES1GRAPES 1PEAR | 1 | 1 | 1 | 2ORANGES1PEAR |
| 1APPLESPEARS 2BANANA ORANGE | 1 | 2 | | PEARSORANGE |
| 1GRAPE1ORANGES PEAR1APPLESPEARS | 1 | | 1 | 1ORANGESPEARPEARS |
