Create new field by lookup two other fields in common (loop function?)

Hi,

I have the follow data where I need to do a cross lookup by group or ID belong to the same owner and create a new identifier. In this example, group A B C & D are linked due to owner's ID 133, and ID 97 and 55 are included because of group C. and 21 are linked due to group B & D. is there a loop function I could use for this practice? I need to look through over 300,000 data to create the new identifier if they have group or ID in common.

Group	ID	owner	Identifier
A	133	abcd	1
B	133	abcd	1
C	133	abcd	1
D	133	abcd	1
C	97	abcd	1
C	55	abcd	1
B	21	abcd	1
D	21	abcd	1

Help

Accepted answers

kelly_gilbert

@KerryWang - this sounds like a great use case for the Make Group tool (help | Tool Mastery), Make Group will find all of the associations between two fields in your dataset.

Original data (I added some rows to create additional groups):

Group	ID	owner
A	133	abcd
B	133	abcd
C	133	abcd
D	133	abcd
C	97	abcd
C	55	abcd
B	21	abcd
D	21	abcd
E	1000	owner2
F	133	abcd
G	9999	owner3
H	1000	owner2

The Make Group tool will output two fields: Group and Key. "Group" is the key that is common across all of the rows (this will be your Identifier field), and Keys are all of the associated values. In your example, the "133" group contains the following keys: 133, A, B, C, D, F, 21 (related through B&D), 55 (related through C), 97 (related through C).

Results after Make Group:

Group	Key
1000	1000
1000	E
1000	H
133	133
133	21
133	55
133	97
133	A
133	B
133	C
133	D
133	F
9999	9999
9999	G

Now that we know all the keys that are somehow related to each other, we can join them back to the ID in the original data. Note that you will now have two Group fields (your original Group and the Group field that came out of the Make Group tool). The Group field that came out of the Make Group tool is your new Identifier.

For another example on how Make Group works, you can find it on the Join tab of the tool palette in Designer. Just right-click on it in the palette --> Open Example.

Make Groups - 2021-06-23.yxmd

kelly_gilbert

@KerryWang - I think the issue in your case is that the field size doesn't match. Both columns should already be strings, since they contain both letters and numbers.

Just include a Select tool before the Make Group (so it looks like my screen shot above), and make sure that the Type and Size columns for Group and ID are the same. You can use the larger of the two sizes (for example, if one is 20 and one is 10, then make them both 20).

The column types should already be one of the string types, but just make sure Group and ID are the exact same type. If you're not sure, you can change them both to V_WString (more on string data types here).

All comments

kelly_gilbert

Group	ID	owner
A	133	abcd
B	133	abcd
C	133	abcd
D	133	abcd
C	97	abcd
C	55	abcd
B	21	abcd
D	21	abcd
E	1000	owner2
F	133	abcd
G	9999	owner3
H	1000	owner2

Results after Make Group:

Group	Key
1000	1000
1000	E
1000	H
133	133
133	21
133	55
133	97
133	A
133	B
133	C
133	D
133	F
9999	9999
9999	G

For another example on how Make Group works, you can find it on the Join tab of the tool palette in Designer. Just right-click on it in the palette --> Open Example.

Make Groups - 2021-06-23.yxmd

KerryWang

Thanks @kelly_gilbert ! I am getting the following error message "The 2 fields must be the same type and size" because my real data has combination of number and string, it looks more like the following:

Group	ID	owner	Identifier
1Ab011	133	abcd	1
1Ab012	133	abcd	1
1Ab013	133	abcd	1
1Ab014	133	abcd	1
1Ab013	9abc71	abcd	1
1Ab013	um55ab1	abcd	1
1Ab012	21ab21	abcd	1
1Ab014	21ab21	abcd	1

DawnDuong

Hi @KerryWang

You can put a Select tool in front to convert everything to String. Then you will not have the incompatibility issue.

You can use Select tool again after the match to convert the desired fields back to numeric.

dawn

kelly_gilbert

KerryWang

thank you @kelly_gilbert I wasn't aware the size need to be consistent as well! Lesson learn!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703