Joining using multiple conditions, including non-exact contains field

Hello everyone.

I am trying to find a way to join two datasets together. One of them is a Work complete status dataset, the other is a master dataset of all the batches (including those in queue). I have tried to find solutions in the forums but with no success. I have done up a dummy dataset with various solutions that I have tried and attached it in this post.

Seeking help from Alteryx gurus over here.

The main obstacle I am facing is when joining these 2 tables, the first table generates the work complete status and date for the "mixing" step regardless of the batch volume. Whereas the main dataset I need to join this datatable with specifies the batch volume under the label column. I also need to retain null values in the master dataset as they are used as identifiers on whether the batch is still pending work completion.

My actual dataset is also very large with multiple "general" processes but with the master dataset having a label that describes the batch process at a finer granularity. There are no other criteria which I can join these 2 datasets on other than Batch ID and Label.

Thank you in advance!

Fuzzy Joining using 2 criteria.yxmd

Datasets

Join

Preparation

Accepted answers

LindonB

Hi @JeeKinn,

See if this solution works for your overall dataset. Basically, I created a join field to take the root part of the master dataset's label too. I used a regex, but you might want to change the approach based on how the " (1000ML)" string might come into your data. My regex assumes that everything from " (" onward is what prevents your joins. (Again, update the logic to your specific use case or map the possible values to a joining label.)

Lindon

Community Post Solution 1303534.yxmd

All comments

LindonB

Hi @JeeKinn,

Lindon

Community Post Solution 1303534.yxmd

JeeKinn

Hi @LindonB ,

Using RegEx Replace for a joining label in the master dataset is a really neat trick! I was contemplating whether to create a joining label field in the master dataset too but there are too many cases to individually label.

For my case, most but not all cases will be accommodated for with your RegEx.

I would like to ask, if there are certain labels that start with [ , e.g. for weight it is [50kg] whereas for volume it is (100mL), how should I modify the logic so that it can take into account several characters that are preventing my join?

JeeKinn

I figured out the RegEx expression to take multiple characters. Implemented it and it is looking alright for now! Thanks @LindonB , I will be marking your reply as the solution.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703