Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Need help modifying a table

Highlighted
5 - Atom

Hi,

I'm working with an excel file that contains each employee's hours, as well as the associated work code for those hours. 

The table looks a bit like this... 

NameWork CodeWeek 1 HoursWeek 2 HoursWeek 3 HoursWeek 4 Hours
Amy12323
Bob13245
Bob34543
Claire20121
Claire32112
Claire43333

 

I want to modify this table so that each employee has a unique row for each of the 4 work codes, even if they work 0 hours on that code.

It should something like this:

NameWork CodeWeek 1 HoursWeek 2 HoursWeek 3 HoursWeek 4 Hours

Amy

12323
Amy20000
Amy30000
Amy40000
Bob13245
Bob20000
Bob34543
Bob40000
Claire10000
Claire20121
Claire32112
Claire43333

 

 

Any tips on how I can get to this?

Thanks!

Highlighted
17 - Castor

Hi @aidanf ,

 

Here is a workflow for the task

 

Output:

atcodedog05_0-1602086767457.png

Workflow:

atcodedog05_1-1602086785434.png


Hope this helps 🙂

 

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Highlighted
16 - Nebula
16 - Nebula

I'd suggest:

jdunkerley79_0-1602087214567.png

 

First, using summarise tools to make the complete list of Names and Work Codes

Next, using append fields to create a full set

A join multiple tool allows for creating an outer join to make the full table

Finally, a multi-field formula allows filling in the NULLs

 

Have attached a sample

 

 

Highlighted
Alteryx Certified Partner

Hey @aidanf 

 

I would suggest a more dynamic approach that would pick up any work code, even if that is greater than that you used in your example

 

Screenshot 2020-10-07 171804.jpg

 

Let me know if that works for you or if anything is unclear.

 

Cheers,

 

Angelos

Highlighted
Alteryx Certified Partner

@jdunkerley79 That's a great approach, couldn't get the Join Multiple tool to work for my workflow, but now I know why. Thanks for that, great solution!

Highlighted
5 - Atom

Thank you so much for this suggestion, it works really well with my workflow.

Labels