Conditional joining using key

Question

Hi,

I have one data set that looks like below:

Employee IDFunction1

Apple2Ball3Cat4Ball5Cat6Apple7Apple

There's another data set that looks like this:

Employee IDSpecialty1Sweet6Sour9Bitter21Tasteless24Juicy

26Salty

I want to add the Column of Specialty to first data set based on matching Employee IDs in the two tables, however only for those Employee IDs that Function as 'Apple'. Some Employee IDs with Function as 'Apple' may not be present in second table (like there's no Employee ID 7 in second table) - in these cases, I want the Specialty to be blank. For Employee IDs with other Functions, I want the Specialty to be blank. So the final output should look like this:

Employee IDFunctionSpecialty1

AppleSweet2Ball 3Cat 4Ball 5Cat 6AppleSour7Apple

Thanks.

Maskell_Rascal · Answer

Hi @Adityasa

If the join you are making is more of a one off/specific use, you can use the attached workflow. If not, I'd need to know more about how you're attempting to filter/join the data.

This workflow uses an Advanced Join macro that enables you to join datasets based on a formula/condition.

Once I get my desired joins, I do a Find/Replace to attach them to the original data by Employee ID.

If this solves your problem please mark answer as correct, otherwise let me know!

Cheers!

Phil

Conditional Join Example.yxzp

Adityasa · Answer

@rkapoor

What if the second table is like this? Here, I don't want the workflow to pick up 3 and 4 as sweet and sour respectively. I want them to be blank.

Employee IDSpecialty1Sweet3Sweet4Sour6Sour9Bitter21Tasteless24Juicy

26Salty

rkapoor · Answer

@Adityasa I think what you are looking to do is similar to a Right Outer Join like in the attached workflow.

This workflow checks the matches and then applies the Speciality column / value, and brings in the other data.

Let me know if this is what you were trying to produce.

Adityasa.yxmd