Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Jonathan_A
5 - Atom

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 !

4 REPLIES 4
rafalolbert
ACE Emeritus
ACE Emeritus

Hi @Jonathan_A,

 

This is a simple Cross Tab:

 

img.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

estherb47
15 - Aurora
15 - Aurora

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
5 - Atom

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
15 - Aurora
15 - Aurora
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