Complex join criteria

[ALT_2358]

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:

A	B	C	D	E	F	TEXT
hjghk	bhghgb	*	bkjbkj	sfddfssdfs	*	TEXT1
*	*	bhkhli	*	*	bjhbjhbhvgv	TEXT2
jhgbjhb	BJHJ	jkbjb	*	*	*	TEXT3
*	*	HVHUV	*	hgtdhht	Hguguysadg	TEXT4

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]

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]

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 · Accepted solution]

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]

Hi @ALT_2358,

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

Good luck!

[ALT_2358]

Thanks, it worked 🙂

[Emil_Kos]

Hi @ALT_2358,

Happy to hear that.