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: ABCDEFTEXThjghkbhghgb*bkjbkjsfddfssdfs*TEXT1**bhkhli**bjhbjhbhvgvTEXT2jhgbjhbBJHJjkbjb***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!

Complex join criteria

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!

Workflow

Datasets

Tips and Tricks

Macros

Common Use Cases

Batch Macro

Fuzzy Match

Join

Preparation

Accepted answers

ALT_2358

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.

All comments

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703