Data from multiple rows

[SonnyDeLyte]

Hi everyone,

Still a newbie trying to figure things out.... how can I transform multiple rows of data into one and also add new columns to the single row with data from the multiple rows?

Original data:

EmpID	Name	Project ID	Month	Hours
111	John Doe	A	June	14
111	John Doe	A	July	16
111	John Doe	B	June	12
111	John Doe	B	July	13
222	Jane Smith	A	June	15
222	Jane Smith	A	July	14
222	Jane Smith	B	June	13
222	Jane Smith	B	July	14

Desired output:

EmpID	Name	Project ID	June Hours	July Hours	2 Month Total Hours	2 Month Avg Hours
111	John Doe	A	14	16	30	15
111	John Doe	B	12	13	25	12.5
222	Jane Smith	A	15	14	29	14.5
222	Jane Smith	B	13	14	27	13.5

Thanks in advance for any help or suggestions!

Sonny

[jdunkerley79]

I would do this using a couple of tools.

Use a Cross Tab tool to rotate the month to be headers

Use a Summarise tool to create the totals and averages

Finally, use a join tool to put together and specify the order of fields.

A quick sample is attached

[cmcclellan]

The Summarize tool should be all you need (http://downloads.alteryx.com/Alteryx/Help/Summarize.htm) for most of it.  To get the "June Hours" and "July Hours" I'd pivot the data and split into a different flow then add it back later (after the summarize).

[SonnyDeLyte]

Hi jdunkerley79,

Thank you for your response - unfortunately, I'm getting an error when trying to download the sample workflow, so I'll see if I can figure it out given your tips.

Thanks!

Sonny

[cmcclellan]

What version of Alteryx are you using ?

[SonnyDeLyte]

Hi cmcclellan,

Version 11.3.2.29874 - on the Windows 10 installation on VMware Fusion on my Mac.

Thanks,

Sonny

[cmcclellan]

Ah ... I see what you mean :O

I thought it was a version problem, but I can't download it either.

I created a sample as well this morning, using v.11.5 running on VMWare on a Mac as well :), but I'm not near that machine at the moment.

I'll post screenshots as soon as I can 

Cheers :)

[SonnyDeLyte]

Great, thank you!!

[vishwa_0308]

Hi,

This is my version for solving this..using cross tab and formula tool

use cross tab and group EmpID, Name & Project ID.

New Column header = [Month]

values for new Columns = [Hours] and aggregating method will be first.

then in formula tool add june and july hours for 2 Month Total

and for 2 Month Average = 2 Month Total/2. Below is the screenshot.

[SonnyDeLyte]

Thank you vishwa - I actually just came to pretty much the same solution myself (using the aforementioned tips from the others) :-)

I'm curious though if not having to use the Summarize tool is due to the simplicity of the test dataset I created...