Assigning features within a group

[kfish]

Hi,

I am working with a very large list of loans (around 20,000) and need help with coming up with logic. Each loan is under a 'parent' facility where the facility plus all loans under it have the same top level code, but different low level (child) codes, and you know what the parent code is by the top level and low level codes being the same. For example, I could have a group of loans where:

Top Level:  Low Level:

0001           0001         --> Facility

0001           0011         --> Loan

0001           0012         --> Loan

0001           0013         --> Loan 

And if one of the codes within this group has a certain feature I want it assigned to the facility since the facility will always have this feature NULL. For example:

Top Level:  Low Level:   Code:                            

0001           0001            NULL            

0001           0011            NULL 

0001           0012            NULL

0001           0013               A

Want to look like:

Top Level:  Low Level:   Code:

0001           0001              A

0001           0011            NULL 

0001           0012            NULL

0001           0013               A

I was thinking possibly of the Make Group tool but not sure if that would accomplish what I'm wanting.

If anyone has any ideas that would be great!

Thank you.

[IraWatt]

hey @kfish,

When you say "if one of the codes within this group has a certain feature I want it assigned to the facility" what do you mean by this? In your example you assign code A

but if Low Level 13 had for instance code B which should be assigned to the Facility, A or B?

[kfish]

Hi! The way this certain feature works is the loans under the facility will always either be NULL or have the same code, so there would not be a case where there is both an A and B code needing to be assigned. 

For example it could look like

Top Level:  Low Level:   Code:

0001           0001            NULL

0001           0011               A

0001           0012               A

0001           0013               A

Or 

Top Level:  Low Level:   Code:

0001           0001            NULL

0001           0011             NULL

0001           0012               A

0001           0013               A

There will never be different codes within the same group.

Hope this makes sense. Thank you!

[IraWatt · Accepted solution]

Ah okay so would a setup like this work for you?

[IraWatt]

Just updated my workflow to group by each top level

[kfish]

I'm thinking this could work! I'm trying it out with my full dataset and need to do validation as it's unfortunately much more complicated than my example obviously but so far looking good!

[IraWatt]

Great stuff ! any issues please say @kfish 

[kfish]

Hi @IraWatt just wanted to let you know this worked, thank you! I do have a quick question tho - what exactly does the sample tool do in this? Unsure of why we need it so would like to know why you used it. Thanks!

[IraWatt]

Glad to hear @kfish ! The main reason is so that when I join the code information back on there is only one record per top level (I group by top level and take the first one for each in the sample). If we had multiple codes per top level every single one would join creating loads more records out of the join.

Great question though because in your example data it made no difference but I assumed you may have a bigger dataset were this could become an issue.

Hope that made some sense, the community has some great interactive videos on joins if your interested?