Joining using multiple conditions, including non-exact contains field

[JeeKinn]

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!

[LindonB · Accepted solution]

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

[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.