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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Convert Rows to Columns

Meteoroid

Hello!

 

I'm hoping someone can help me convert certain rows into columns and concatenate other rows. Here is my data:

 

SOURCE_APP

DAY

DC_CODE

Sum_AMT

010_RO

01

Credit

-125503.95

010_RO

01

Debit

125503.95

005_CT

03

Credit

-475638.39

005_CT

03

Debit

475638.39

006_TB

03

Credit

-3794473.35

006_TB

03

Debit

3794473.35

005_CT

04

Credit

-395187.48

005_CT

04

Debit

395187.48

006_TB

04

Credit

-3836339.96

006_TB

04

Debit

3836339.96

010_RO

04

Credit

-2286371.48

010_RO

04

Debit

2286371.48

005_CT

05

Credit

-454366.36

005_CT

05

Debit

454366.36

006_TB

05

Credit

-4189221.52

006_TB

05

Debit

4189221.52

 

I would like to concatenate SOURCE_APP and DC_CODE and convert the DAYS into columns.  Here is a mockup of what I want my output to look like:

 

SOURCE_APP_DC

1

2

3

4

5

005_CT_CREDIT

  

-475638.39

-395187.48

-454366.36

005_CT_DEBIT

  

475638.39

395187.48

454366.36

006_TB_CREDIT

  

-3794473.35

-3836339.96

-4189221.52

006_TB_DEBIT

  

3794473.35

3836339.96

4189221.52

010_RO_DEBIT

-125503.95

  

-2286371.48

 

010_RO_CREDIT

125503.95

  

2286371.48

 

 

Thanks!

 

Highlighted
Asteroid

Hello @tfinn

 

This is a nice one with Alteryx. You want a formula tool to add your columns "Source_App" and "DC_CODE". You then use the cross tab tool which you find under transform. Use this new key field as your grouping, day as column headers and SUM_AMT as value.

 

Hope this helps

 

thanks

 

chris

 

Alteryx Certified Partner

A Formula tool can create the "SROUCE_APP_DC" field and Cross Tab will rearrange the data no problem. The tricky part to this is to fill out the table with all possible fields (since day 2 doesn't appear in the data).

 

In the attached solution I used a couple Summarize tools and a Generate Rows to create all the possible fields to join into the data before the Cross Tab.

Meteoroid

Thank You! 

Meteoroid

Thank You @CharlieS. I only uploaded a portion of my data so this extra step isn't necessary but your note was still very informative.

Labels