Alteryx Designer Discussions

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

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
16 - Nebula

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