Join Tool - Need 2 amounts from 1 row split into 2 separate rows after join

[TKrueger]

Hello, I am trying to pull in a value from a specific row from my right join input but the row name isn't the same as the row in my left join input. I need to use 2 separate values from the same row in my right join input data set but the 2 values need to go into 2 different rows after the join See attached excel file for more details. The input tab shows how the data is on my input (right input into my join tool) and the output tab shows how I need it (left input in into my join tool) after the join. On the input tab all values are on the 07 CO row but then I need to split the values onto the 07 CO and 07 DN rows going forward after my flow. The $129.87 in the citytaxamount column needs to flow to the 07 DN row after my join (there is no 07 DN row in the input data, it is all lumped into the 07 CO row and this is a ledger pull so tough to change). The remaining $108 needs to go into the 07 CO row after the join.  I am not sure if this is possible or if I have to manipulate the data somehow prior to the join tool. Any help would be appreciated. Thank you in advance. 

[Luke_C]

Hi @TKrueger 

You definitely need to do some work ahead of any joins. You'll need additional tables to dictate the various jurisdictions (states, counties, cities, etc) that include attributes like if its a monthly or annual filing, abbreviations, etc. 

From there you can map the different columns in your data to the corresponding values, although you might need more detail. For instance you have company/state and a field for city taxes.. your example just has Denver but if there's a second city collecting tax in CO I'm not sure how you'd split it.