community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

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

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 :

 

IDAMOUNTTRANSACTION TYPE
20190710377340000INVCAP
2019071037730HON
2019071037730FEE
2019071037730OTHER
20190710370920000DISTRIB
2019071037091000PROFIT
2019071037090INVCAP
2019071039453600HON
2019071039450INVCAP

 

I'm looking to obtain this :

 

IDINVCAPHONFEEOTHERDISTRIBPROFIT
2019071037734000000000
2019071037090000200001000
201907103945036000000

 

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 !

Bolide

Hi @Jonathan_A,

 

This is a simple Cross Tab:

 

img.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

Pulsar

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

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 ?

Highlighted
Pulsar
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
Labels