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

Looking up and Down a Hierarchy to Answer Questions

smoskowitz
12 - Quasar

Good morning Community –

 

I have a challenge that I have been grappling with for a few days that I have yet to crack and wanted to solicit some ideas from all of you.

What I am trying to do is for each Record ID, answer some questions about the Base Entity. A base entity is any entity at tier 0 and all the units above it is, in some way, its owners. A base entity will have a Regarded Owner(s), a Regarded CFC(s), First Tier CFC(s) and a US Shareholder(s).

Also, the below represents some of the more complex scenarios, most of the work is a lot more linear (except for the top-tier CFC) then this, but these are troubling me the most.

Any time you see Tiers listed more than once, that represents a split-ownership. So, in Scenario 1:

  • Tier 2 AU700A is owned by both AU700 and T-TNH

Scenario 1

    
      

RecordID

TIER

GTN

CY_ENTITY TYPE

  

21

6

T-TNC

US

<--USSH

 

21

5

NL881

CFC

<-- First Level CFC

 

21

4

NL123

FDE

  

21

3

AU700

CFC

  

21

3

T-TNH

US

<--USSH

 

21

2

AU700A

CFC

<-- Regarded CFC & First Level CFC

21

1

AU700B

CFP

<-- Regarded Owner

 

21

0

AU07390

FDE

<-- Base Entity (always Tier 0)

 
      
      
      
      

Scenario 2

    
      

RecordID

TIER

GTN

CY_ENTITY TYPE

  

193

5

BSR

US

<--USSH

 

193

5

IPG

US

<--USSH

 

193

4

CI414

CFC

<--First Tier CFC

 

193

4

IPG

US

<--USSH

 

193

3

BSR

US

<--USSH

 

193

3

CI414C

CFC

<--First Tier CFC

 

193

3

T-TNC

US

<--USSH

 

193

2

BSR

US

<--USSH

 

193

2

CO580

CFC

<--First Tier CFC

 

193

2

IPG

US

<--USSH

 

193

1

CO658

CFC

<-- Regarded Owner, Regarded CFC, and First Tier CFC

193

0

VN658

BRANCH

<-- Base Entity

 
      
      

 

 

 

 

 

Questions I Need to Answer:

  

1) Who is the Base Entities Regarded Owner?

 

2) Who are the Base Entities Regarded CFC?

  

3) Who Are the Base Entities First Level CFC?

4) Who are the US Shareholder?

  
      

Rules

     

Regarded Owner is a CFP or CFC that is closest in the structure to the base entity. (AU700B is the regarded owner

Regarded CFC is a CFC that is closest to the Base Entity. (AU700A is the regarded CFC)

A First Level CFC is a CFC who's direct Owner is a US company (AU700A and NL881 are first level CFC's)

A US Company is a US Shareholder (USSH)

  

 

Challenge

  1. In scenario 1, I have been trying to figure out a way to look up the entire chain to get to the top level CFC’s and have not had much luck. So starting with Tier 3 (AU700) which is a CFC, I need to look the rest of the way up the structure to see if there are any more CFC’s – which there is. I then need to look above that and see if there are any CFC above that or a US entity. If there are more CFC’s then I need to keep looking up the chain, id it a USSH, then I am done. I though using a Max tier would work, but then I would miss Tier 2 AU700A which is also a First Tier CFC.
  2. Once I find a regarded owner, I should stop looking for a regarded owner going forward.

I was thinking some sort of iterative macro, but not sure how to look up and down the entire structure. Not just one above or one below. Maybe there is a way using an iterative macro with a Multi-Row formula tool, but not sure about that either.

 

At the end of the day, I would like to see in a new column (it could be a seperate column for each question) which entity satisfies the answer to one of my questions.

 

Thanks,

Seth

1 REPLY 1
GeneRinas
Alteryx
Alteryx

Hi Seth,  Here is the workflow that answers the questions.  Great talking with you today.

Labels