Dynamic Balance Reclassification/Movement across Entities and Columns

Shoaib_Shaikh

Hi Alteryx Community,

I am looking for help with a use case involving dynamic balance reclassification. I need to move balances between different Entities based on a secondary Adjustment/Input Table.

The challenge is that the column where the movement happens, the source entity and the target entity are all dynamic.

1. Current Data (Base Table)

I have a dataset with multiple balance columns. Here is a sample:

Entity	Recon Balance	AP Balance	AR Balance
Netherlands	100	200	0
Saudi	200	0	0
Poland	100	100	400
UAE	0	100	200
Australia	600	200	100

2. The Logic (Adjustment/Input Table)

The user provides a "Control/Input Table" that specifies which column to adjust and which entities are the Source and Target. If a Source is selected, that value should be subtracted from the Source Entity and added to the Target Entity for that specific column. Where Target entity is not specified, that will make Source zero and no balance transfer will happen.

Select Column Name for Balance Movement	Source Entity	Target Entity
Recon Balance	Netherlands	Australia
Recon Balance	Saudi	Poland
AP Balance	Australia	Netherlands
AR Balance	Poland
AR Balance	UAE	Saudi

3. Expected Output

Based on the logic above, the Australian Recon Balance would increase from 600 to 700 (taking 100 from Netherlands), and so on.

Entity Recon Balance AP Balance AR Balance

Netherlands	0 (Moved to Aus)	400 (Rec'd from Aus)	0
Saudi	0 (Moved to Pol)	0	200 (Rec'd from UAE)
Poland	300 (Rec'd from Sau)	100	400
UAE	0	100	0 (Moved to Sau)
Australia	700 (Rec'd from Neth)	0 (Moved to Neth)	100

OTrieger

@Shoaib_Shaikh 

Alteryx can do that, what you will need to do is merge the data 2 sets 2 times, 1 time based on the source Entity and 1 time based on the Target Entity. Based on these 2 data mergers you will have the needed data aligned to get it done.

What was your thought process to get it done?

Which Alteryx tools are you familiar with to get it done?