This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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]
@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.
Ah, OK. So you really need to keep looping through, line by line, to find the matches, and then calculate the totals.
I do think a macro would be the way to do this, and perhaps an iterative macro. If you only need to look to the previous tier to pull in the value based on the parent to GTN match, you could keep filtering through the data and calculating row by row, I think.
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.
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.
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.
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.