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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

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

Alteryx Partner

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?

Magnetar
Magnetar

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

Alteryx Partner

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 ?

Magnetar
Magnetar
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