Crawling Down an Org Structure

Hi All --

I have spent the last 2-3 days trying to figure out a way to accomplish a task given to me and have yet to have any luck. Below is an org structure normalized. Record ID is the group (I am only showing group 88 but there are 949). Tier represents the level -- 0 is the lowest level and in this case 5 is the highest.

I am given the Tier 0 Total_Final amount -- in this case 1,703,823.849. Here is the concept:

Total_Final is multiplied by InterestPC so in the case of Tier 0 -- the Total Based Ownership is now 8519.

Once that is done, we match up the 1st parent in Tier 0 to the GTN in Tier 1. So, in this case Tier 0's 1st parent is BE318 matches up to two GTN's in Tier 1. Once we match it the The Tier 0 -- Total Based on Ownership amount gets moved to tier 1 Total_Final. In the example below 8519 on tier 0 gets moved to Tier 1's Total Final. Confused yet?

Tiers can vary in length.

Excel file attached. I can attach my batch macro, but I'm not sure it helps.

Thanks,

Seth

Record ID	TIER	GTN	Tax Type	Right_Child and Parent Together	InterestPc	1st Parent	ChildParent	Total_Final	Total Based on Ownership
88	0	BE583B	CFC	BE583BBE318	0.005	BE318	BE583BBE318	1703823.849	8519.119243
88	1	BE318	CFC	BE318UK318	0.995	UK318	BE318UK318	8519.119243	8476.523646
88	1	BE318	CFC	BE318UK990	0.005	UK990	BE318UK990	8519.119243	42.59559621
88	2	UK318	FDE	UK318UK990	1	UK990	UK318UK990	8476.523646	8476.523646
88	2	UK990	CFC	UK990UK300	1	UK300	UK990UK300	42.59559621	42.59559621
88	3	UK300	FDE	UK300UK990K	1	UK990K	UK300UK990K
88	3	UK990	CFC	UK990UK300	1	UK300	UK990UK300
88	4	UK300	FDE	UK300UK990K	1	UK990K	UK300UK990K
88	4	UK990K	FDE	UK990KUK970	1	UK970	UK990KUK970
88	5	UK970	CFC	UK970UK07351	1	UK07351	UK970UK07351
88	5	UK990K	FDE	UK990KUK970	1	UK970	UK990KUK970

Tier Example.xlsx

Workflow

Batch Macro

Accepted answers

estherb47

@smoskowitz ,

Thank you for the further clarification. This macro should work.

As @danilang wrote, it needs to be an iterative macro, because we're not sending separate records through, we're cycling through an existing list.

Iterative macros are like a loop until done. Batch macros take new information in each time.

Leveraging the iteration number (which conveniently starts at 0, matching your first tier), the first run will grab Tiers 0 and 1, join the GTN from Tier 1 to the Parent of Tier 0, and use the Total Based on Ownership from the match. That field will be renamed Total_Final, and a new Total Based on Ownership is calculated. Those rows will now be unioned together, and sent to the output table.

The new data for Tier 1 rows is now unioned back with the remaining tiers, and the macro runs again. The next run will look at Tiers 1 and 2, and the loop will continue until all records are exhausted.

Try the attached and let me know if it works.

Cheers!

Esther

ParentChildPull.yxzp

All comments

estherb47

Hi @smoskowitz

So the description of the parent to GTN is a little confusing ("we match up the 1st parent in Tier 0 to the GTN in Tier 1" to me would mean BE318BE318 for the second row), but it looks like you're pulling the Total Based on Ownership from 2 rows above into the Total_Final column, and multiplying it by the interest pct to get the new Total Based on Ownership.

If I'm totally off base, let me know please.

Multi-Row formula tool could do this for you.

[Total Based on Ownership] calculation: [InterestPc]*[Row-2: Total Based on Ownership]

You could add another MultiRow formula tool to pull in the [Total_Final] as simply [Row-2: Total_Final]

Let me know if that helps!

Cheers,

Esther

smoskowitz

@estherb47 I think you got it. When you match the First parent from tier 0 to the gtn on tier 1 the tier 1's total_final should now be the tier 0's Total based on ownership. Now when I match up tier 1's 1st parent (lets say UK318 to tier 2's GTN (UK318) its total_final should become 8476...and so on.

However, the number of records per tier are not always 2. I have tiers that have 3, 4, or 5 records so I am not sure how the multi-row would help if the tier to record are not consistent. Could be I just don't know how to use the multi-row to be more dynamic.

Thanks,

Seth

danilang

Hi @smoskowitz

In your original post you mentioned that the number of rows in the tiers can vary. Since this is the case, any solution based on a multi-row tool will eventually run across a case where there's an extra row, or a row missing, so all your counts get messed up.

Any time you're trying to traverse a tree structure, you're firmly in Macro territory. Not only that, but unless you have a very well behaved tree with a fixed number of tiers, you actually need an Iterative macro as opposed to a batch macro. With a batch macro you need to be able to determine the number of iterations required before dropping into the macro. Because of it's very nature, a tree doesn't let you calculate this before you actually traverse it.

The progression logic in a iterative macro is contained within the macro itself. In each iteration, the process decides whether to continue or not. Are there children? Then traverse the children. If not, exit.

Since you mentioned that you're using a batch macro that you didn't attach, consider changing it to an iterative one and reworking the logic so that the current tier is processed and output and the tiers below are sent on to the next iteration. You situation is a little tricky, because you need to pass information from one tier to the next. In this case, you'll output the results for the current tier and also pass it to the next iteration. So in each iteration N you have the records for N-1, N and all following tiers(N+x). You calculate the N tier records using the N-1 records, pass the N tier one to the output and pass only the N and N+x to the next iteration.

Hope this helps

Dan

smoskowitz

For more clarity, I mentioned I was using a batch macro. The batch macro is bringing the data in by record ID. So, in this case, Record ID 88. I also thought an iterative macro would be good, but I have a logic issue. So the first iteration I bring in Tier 0 and 1. Using the information from tier 0, it gives me the total final and I calculate the Total based on ownership.

However, when I iterate, I then bring in Tier 1 and tier 2, but it brings in tier 1 data from the source and loses all of the tier 1 calculations I did. I am unsure how to use the calculated tier 1 (where to store it) with tier 2) and so forth. Should I write to a file and read back in? I'm not sure.

Thanks,

Seth

danilang

@smoskowitz

If you change the type of macro to an iterative one, you actually control the records that you pass from one iteration to the next. That's what I was referring to when I said that you pass the N and N+x records to the next iteration. Any changes that you make to N show up as N-1 on the next iteration.

An iterative macro has at least 2 outputs. The 1st( or possibly several, depending on what exits the macro) is the one that exits the macro where the results from each iteration are unioned. This is where you send the N records. The second output is known as the iterative output and actually loops around to the macro input. This is where you send the the N records including any changes you've made to them and the N+1 records.

The iteration process stops when you stop sending records to the next iteration. As long as you have children, you pass the current level plus the children to the next iteration. On the last level you send the changed records out the main output and send no records to the iterative output.