Data Manipulation - Debit/Credit Logic

[kkkim]

Hi, Alteryx experts!

I am trying to find a way to manipulate the data to output credit/debit results.

So data will look something like this:

Client Name	Sale Date	Item Bought	Amount Bought	Item Sold	Amount Sold
John Smith	1-Jan	Socks	400	Hats	-100
John Smith	1-Jan	Hats	100	Socks	-450
John Smith	1-Jan	Shirt	20	Shoes	-10
John Smith	1-Jan	Shoes	50	Shirt	-20

I would need to output something like this:

Client Name	Sale Date	Socks	Hats
John Smith	1-Jan	400	100
John Smith	1-Jan	-450	-100
		-50	0
Client Name	Sale Date	Shirt	Shoes
John Smith	1-Jan	20	50
John Smith	1-Jan	-20	-10
		0	40

Any thoughts on how to accomplish this? Would I use Transpose/Cross Tab?

Thank you so much for your help in advance!

[IraWatt]

Hey @kkkim,

There is probably a more refined way of doing this but here is one way:

I use the multirow formula tool to link related rows then the cross tab tool to pivot the data into the correct orientation. Then I summarize the rows to get the totals and union it all together. I also use the multi field tool to get the headers from the second table.

This solution uses the transpose and cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

 Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[grazitti_sapna · Accepted solution]

@kkkim , here is another way of doing this. Please let me know if you liked my solution. I simply used the field info tool to get fields to data rows.

Thanks!!

[kkkim]

Thank you so much for your help, everyone!