How can I agregate and reorganize this data to obtain the desired result ?

[Jonathan_A]

Hi there,

I'm currently working on a workflow that needs me to agregate and reorganize data based on non-unique ID.

Here is my input :

ID	AMOUNT	TRANSACTION TYPE
201907103773	40000	INVCAP
201907103773	0	HON
201907103773	0	FEE
201907103773	0	OTHER
201907103709	20000	DISTRIB
201907103709	1000	PROFIT
201907103709	0	INVCAP
201907103945	3600	HON
201907103945	0	INVCAP

I'm looking to obtain this :

ID	INVCAP	HON	FEE	OTHER	DISTRIB	PROFIT
201907103773	40000	0	0	0	0	0
201907103709	0	0	0	0	20000	1000
201907103945	0	3600	0	0	0	0

Basically I want to transform the different values of the TRANSACTION_TYPE column (there are 20 in my original input) into columns, and allocate each amount to its proper column based on TRANSACTION TYPE, in order to have a unique ID in the end result.

I have no idea how I can do this. Any help is welcome !

[rafalolbert · Accepted solution]

Hi @Jonathan_A,

This is a simple Cross Tab:

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[estherb47 · Accepted solution]

Hi @Jonathan_A 

Just to add a little more to @rafalolbert 's correct answer, you'd put the ID as a grouping level, the Transaction Type as the Header columns, and the Amount in the Value (use a sum to add).

Cheers!

Esther

[Jonathan_A]

That's exactly what I needed. Thank you very much.

However, If I have more columns in my input similar to the ID, how can I have them coming out of the crosstab without them factoring into the end result ?

[estherb47]

Hi,

If the field would roll up the same way as the ID field, you could have multiple Groiping fields.

Otherwise, a join can help pull other fields back in.

Cheers,
Esther