Creating a Formula using Two Workflow Outputs

[ebledsoe22]

Hello. I am working on a state apportionment workflow to increase efficiency. I have the gross factor numbers and now I am working on the adjustments. I have two workflows on my canvas. The first workflow produces three columns (Account number, State, and Value) and a total row for the values. The second workflow produces three columns (Account number, State, and Percentage). I need to multiple the total from my first workflow by the percentages in my second workflow. What is the best way to do this? 

[ebledsoe22]

The account numbers and states in each of the workflows are different. Ultimately, I will be using the states from the second workflow moving forward.

[PanPP]

Hi @ebledsoe22 

You can use the join capabilities to accomplish this. What is common between the 2 datasets? 

If the Account Numbers are the unique ID between the two datasets, you can join the data by specific fields on the Account Number column.

If the account numbers aren't an option as the Unique ID, then you can use the States column to merge the datasets together. 

You can then use a formula tool to achieve your desired output.

https://help.alteryx.com/20223/designer/join-tool

[ebledsoe22]

Hi @PanPP . Thank you for your response. I tried using the Join Tool and chose "Join by Record Position" which gave me what I wanted, but it left some of the data off since one workflow produced a dataset with 9 rows, while the other only had 5. I've been trying to see if I can just generate some place holder rows to increase the number of rows to 9, but I can't seem to figure out the generate row tool. Can you help me apply the generate row tool? I guess the Account number for the place holder rows could just add one to the last account number (14300 + 1, 14300 +2, etc.) in the dataset and the other two columns can be empty or null since I just need it as a place holder to make the join tool work.

[ebledsoe22]

@PanPP . This is what I have so far, but it keeps telling me, "The field "Account" is missing. Compare the tool configuration with the input stream." I don't know what this means or how to fix it.