Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Need help modifying a table

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

5 REPLIES 5
atcodedog05
22 - Nova
22 - Nova

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

jdunkerley79
ACE Emeritus
ACE Emeritus

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

 

 

AngelosPachis
16 - Nebula

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

AngelosPachis
16 - Nebula

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

aidanf
5 - Atom

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

Labels