Looking for Starter Kits? Head to the Community Gallery! Now formatted as YXIs for easy installation.

Alteryx Designer Desktop Discussions

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

Data from multiple rows

SonnyDeLyte
7 - Meteor

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:

EmpIDNameProject IDMonth

Hours

111

John Doe

A

June

14
111John DoeAJuly16
111John DoeBJune

12

111John DoeBJuly13
222Jane SmithAJune15
222Jane SmithAJuly14
222Jane SmithBJune13
222Jane SmithBJuly14

 

Desired output:

EmpIDNameProject IDJune HoursJuly Hours2 Month Total Hours2 Month Avg Hours
111John DoeA14163015
111John DoeB12132512.5
222Jane SmithA15142914.5
222Jane SmithB13142713.5

 

Thanks in advance for any help or suggestions!

Sonny

17 REPLIES 17
jdunkerley79
ACE Emeritus
ACE Emeritus

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
13 - Pulsar

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

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
13 - Pulsar

What version of Alteryx are you using ?

SonnyDeLyte
7 - Meteor

Hi cmcclellan,

 

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

 

Thanks,

Sonny

cmcclellan
13 - Pulsar

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

Great, thank you!!

vishwa_0308
11 - Bolide

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.

 

pic.png

SonnyDeLyte
7 - Meteor

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

 

 

Labels