Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Matching text contained in one field to a value in another field

Highlighted
7 - Meteor

Hello,

 

I am trying to make sure Product Descriptions in our system match up with the values in another field, Pack Size. My company has a standard for hosting the Pack Size in the Product Description. For example if an item is a 12 Pack of Bud Light, then the Pack Size would be 12 and the Product Description would read something like: SELTZER VARIETY 12PK.

 

My goal is to identify items that have a Product Description that doesn't match up with the Pack Size field. For example SELTZER VARIETY 8PK when the Pack Size is 12. I'm also interested in catching instances where the Pack Size is missing from the Product Description altogether.

 

I've been trying to flag items where the Pack Size doesn't match up with what's provided in the Product Description. I've used the CONTAINS function, but that hasn't been getting me any results. Specifically I tried different variations of this formula:

 

 if contains('[PACK_SIZE]', [PRODUCT_DESCRIPTION]) then 'YES' else 'NO' endif

 

Does anyone have a better way to text match the value in Pack Size to the value that's present in the Product Description?

 

Thank you.

Highlighted
Alteryx Certified Partner

Hi @anthony123 ,

 

Obviously you need to feed this back to the system administrators and get them to standardise this because this is a perfect example of awful data capture. You need to have data standardisation for this, and I'm going to suggest something which will only fix a sub-set of your problems. The reason is you have no way of determining if a number in the description is your pack size.

 

However, try the following and see how you get on:

 

mceleavey_0-1592956900534.pngmceleavey_1-1592956924208.png

I've input the data in the text input as above. I've then given the rows an ID, used regex to isolate just the numeric values, and joined this parsed value back to the primary stream on the ID and the pack size to the parsed description value to match the numbers.

I've included different possibilities that will cause problems, but you may not have those, I just included them to highlight a problem.

 

I've attached the workflow.

Hope this helps.

 

M.

 

 

Highlighted
7 - Meteor

Hi @mceleavey 

 

Thanks for your response. I agree. Unfortunately, our systems are quite outdated and limited.

 

I'm not very familiar with regex. To add another hurdle, I've just discovered that for alcoholic beverages, we also host fluid ounces (FZ) in the PRODUCT DESCRIPTION. How do you recommend I adjust the regex to account for those situations?

 

Example: 

 

LAGER VALVE STEM 16FZ CAN 4PK

 

Thanks,

John

Highlighted
Alteryx Certified Partner

Hi @anthony123 ,

 

yeah, this is the perfect example. Your data is unrelated, so there's no logical way of doing that. That data says there are 4 cans in a pack and nothing else. The fz is not related to number of cans.

Labels