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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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
Bolide

Hi @Jonathan_A,

 

This is a simple Cross Tab:

 

img.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

Pulsar
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 ?

Pulsar
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