Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Complex join criteria

ALT_2358
9 - Comet

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!

6 REPLIES 6
Emil_Kos
17 - Castor
17 - Castor

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
9 - Comet

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
9 - Comet

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
17 - Castor
17 - Castor

Hi @ALT_2358,


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

 

Good luck!

ALT_2358
9 - Comet

Thanks, it worked 🙂

Emil_Kos
17 - Castor
17 - Castor

Hi @ALT_2358,

 

Happy to hear that. 

Labels