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

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

KerryWang
6 - Meteoroid

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.

 

GroupIDownerIdentifier
A133abcd1
B133abcd1
C133abcd1
D133abcd1
C97abcd1
C55abcd1
B21abcd1
D21abcd1
5 REPLIES 5
kelly_gilbert
13 - Pulsar

@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):

GroupIDowner
A133abcd
B133abcd
C133abcd
D133abcd
C97abcd
C55abcd
B21abcd
D21abcd
E1000owner2
F133abcd
G9999owner3
H1000owner2

 

 

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:

GroupKey
10001000
1000E
1000H
133133
13321
13355
13397
133A
133B
133C
133D
133F
99999999
9999G

 

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. 

 

kelly_gilbert_0-1624462309207.png

 

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.

KerryWang
6 - Meteoroid

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:

 

GroupIDownerIdentifier
1Ab011133abcd1
1Ab012133abcd1
1Ab013133abcd1
1Ab014133abcd1
1Ab0139abc71abcd1
1Ab013um55ab1abcd1
1Ab01221ab21abcd1
1Ab01421ab21abcd1
DawnDuong
13 - Pulsar
13 - Pulsar

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
13 - Pulsar

@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).

kelly_gilbert_1-1624502502565.png

KerryWang
6 - Meteoroid

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

 

Labels