Say I have the following data sample:
| ClientID |
Transaction |
Description |
| 001 |
1 |
green leather |
| 001 |
2 |
red car |
| 001 |
3 |
black sky |
| 002 |
1 |
grey shirt |
I want to add a new field with information about the client using wildmatching and a mapping table, printed below:
[model]:
LOAD * INLINE
[
pattern, model_name
*leather*, CLOTHING
*sky*, AVIATION
*car*, MOTOR
*shirt*, CLOTHING
];
How can I create the desired output using the mapping table:
| ClientID |
Transaction |
Description |
model_name |
| 001 |
1 |
green leather |
CLOTHING |
| 001 |
2 |
red car |
MOTOR |
| 001 |
3 |
black sky |
AVIATION |
| 002 |
1 |
grey shirt |
CLOTHING |
My current solution uses a nested if statement, but it is not a clean solution and is not sustainable long-term when the table gets bigger.
if(WildMatch(Description, '*leather*', '*shirt*'), 'CLOTHING
if(WildMatch(Description, '*sky*'), 'AVIATION',
if(WildMatch(Description, '*car*'), 'MOTOR',
'UNIDENTIFIED'))) as model_name
