Convert Rows to Columns

[tfinn]

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!

[clant · Accepted solution]

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

[CharlieS]

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.

[tfinn]

Thank You! 

[tfinn]

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.