Match intercompany transactions

[johnny_jy]

Hey, may I ask if someone may know how to match for following scenario please?

Basically, company A has 200 receivable from company B. Company B need to pay 200 to Company A.

Account category	Account	Intercompany	Company A	Company B
Account receivable	1000	Company A	0	0
Account receivable	1000	Company B	200	0
Account payable	2001	Company A	0	-100
Account payable	2002	Company B	0	0
Account payable	2001	Company A	0	-100
Account payable	2002	Company B	0	0

I would like Alteryx to give me a summarise table like following

	Company A & B	Note (No need to include in Alteryx)
Current asset mismatch	0	200-100-100

Thanks a lot!

[abacon · Accepted solution]

@johnny_jy Just summarize the 2 dollar value columns, use a formula to compare and create your identifying text column, then remove the columns you don't need.

Bacon

[RWvanLeeuwen · Accepted solution]

Hi there,

I built upon the prior suggestion of @abacon and I feel it might be appropriate to look at how I pivoted the data for this use case. 

Have a look at attached file, and the image here

[johnny_jy]

many thanks for your help :)

[johnny_jy]

Thanks a lot :)

[RWvanLeeuwen]

You are most welcome. Happy solving!