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 |
Solved! Go to Solution.
@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.
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 |
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
@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).
thank you @kelly_gilbert I wasn't aware the size need to be consistent as well! Lesson learn!