Condensing Data

I am working on a workflow in which I have to break down employees hours based on Regular hours worked, OT hours worked, and PTO Hours. I then joined them and instead of all the hours being on the same row with different column names it added extra rows per hours. I tried using a cross tab tool but it didn't work. I have attached the output and the desired output, to see if any one has any suggestions as to how I can get to my desired outputs.

TIA

Sum of hours.xlsx

Desired Results- sum of hours.xlsx

Sum of hours WF screenshot.png

Workflow

Output

Tips and Tricks

Accepted answers

Lizbhernan

I'm doing it like that because for regular hours there are a ton of pay codes that would be considered regular and if I split it by pay code then I would be grouping them by pay code. However, you just gave me an idea and I will add an extra column on the mapping table that will allow me to summarize based whether it's regular, OT or PTO.

jrlindem

@Lizbhernan Did that end up working for you? -Jay

All comments

jrlindem

I'm not sure I follow why you need to split up the data into different sections to aggregate....

Couldn't you just run the entire feed into a Summarize Tool. Group by Employee ID and then SUM each of the other fields?

In your picture you area splitting and union'ing which is stacking the data, not joining in the traditional sense. But I don't think you need to do even that....

-Jay

Lizbhernan

jrlindem

Yes, that's a great idea. Allowing you to group by some sort of flag field is a great approach. -Jay

jrlindem

@Lizbhernan Did that end up working for you? -Jay

Raj

@Lizbhernan
your input and desired output have different numbers of rows and i am not sure what the logic was that you used to pull the number of working hours and OT hours but PTO hours are visible.

Lizbhernan

Yes, that worked. Thank you for your help! 😊

PeterGoldey

Yes, I use flagging like this all the time.

One thing I like to do is flag with 0 and 1. That way, the flag itself can become useful for summarizing counts.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703