ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
2 Day Countdown - The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MDT. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Complex join criteria

ALT_2358
8 - Asteroid

Hi, 

 

I have a dataset to which I need to apply some mappings. 

 

However, I can't use the join tool on its own for this as the mappings are based on some/all fields depending on whether an asterisk has been recorded (essentially acting as a sort of wildcard). 

 

Here is what the mapping table looks like:

ABCDEFTEXT
hjghkbhghgb*bkjbkjsfddfssdfs*TEXT1
**bhkhli**bjhbjhbhvgvTEXT2
jhgbjhbBJHJjkbjb***TEXT3
**HVHUV*hgtdhhtHguguysadgTEXT4

 

Looking at the first mapping as an example, we'd need A, B, D and E to match in the dataset, returning 'TEXT1' if so. C and F can be any value. If TEXT1 doesn't return a match, we'd need to check the second mapping. 

 

This mapping table can grow, so it needs to be valid for all possible combinations. 

 

Any help would be much appreciated!

Emil_Kos
15 - Aurora

Hi,

 

It looks like you still needs to use the join tool but instead of 1, you need to use 4 of them. Thanks to that you will replicate *.

 

You need to do 4 of them as each of the joins you showed require using diffrent columns. 

 

Alternatively, you can write formulas IF A = AND B= And d + And E = then 'Text1' 

 

Both should work for you. 

 

 

ALT_2358
8 - Asteroid

Thanks for the quick reply. 

 

The problem is that I need to account for any combination - if my math is correct, that's 5! = 120 combinations of the 5 fields, so I'd need 120 join tools. 

 

 

ALT_2358
8 - Asteroid

I think I've found a way...

 

Adding a record ID then appending the entire mapping table to the dataset, using some formulae to work out if there's a match followed by a find and replace against the record ID back to the original data. 

Emil_Kos
15 - Aurora

Hi @ALT_2358,


Glad to hear. I think you are correct and your idea should work. 

 

Good luck!

ALT_2358
8 - Asteroid

Thanks, it worked 🙂

Emil_Kos
15 - Aurora

Hi @ALT_2358,

 

Happy to hear that. 

Labels