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

Crawling Down an Org Structure

smoskowitz
12 - Quasar

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 IDTIERGTNTax TypeRight_Child and Parent TogetherInterestPc1st ParentChildParentTotal_FinalTotal Based on Ownership
880BE583BCFCBE583BBE3180.005BE318BE583BBE3181703823.8498519.119243
881BE318CFCBE318UK3180.995UK318BE318UK3188519.1192438476.523646
881BE318CFCBE318UK9900.005UK990BE318UK9908519.11924342.59559621
882UK318FDEUK318UK9901UK990UK318UK9908476.5236468476.523646
882UK990CFCUK990UK3001UK300UK990UK30042.5955962142.59559621
883UK300FDEUK300UK990K1UK990KUK300UK990K  
883UK990CFCUK990UK3001UK300UK990UK300  
884UK300FDEUK300UK990K1UK990KUK300UK990K  
884UK990KFDEUK990KUK9701UK970UK990KUK970  
885UK970CFCUK970UK073511UK07351UK970UK07351  
885UK990KFDEUK990KUK9701UK970UK990KUK970  
8 REPLIES 8
estherb47
15 - Aurora
15 - Aurora

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
12 - Quasar

@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

estherb47
15 - Aurora
15 - Aurora

Hi Seth,

 

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.

 

Let me see if I can solve.

 

Cheers,

Esther

 

danilang
19 - Altair
19 - Altair

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
12 - Quasar

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
19 - Altair
19 - Altair

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

 

Dan

estherb47
15 - Aurora
15 - Aurora

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

image.png

 

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

smoskowitz
12 - Quasar

Hi @estherb47 --

 

While it didn't get me exactly where I wanted to be, it led me down the right path and with a few tweaks - I got it to work! So thank you very much!!

 

Seth

Labels