Trying to Summarize and remove nil data

[krishnagandhi]

Hi All,

So what i am trying to do is i have a workflow where there are negative hours input and sometimes that is to correct hours input under the wrong job description. Downstream this is having a negative impact so what i am trying to do is clean up the data so that any hours that net out are removed. If you see below i have created a workflow and gotten to a certain point with the data:

But now i need the data to be summarized as follows:

I can't figure out how to summarize it this way. Any help would be most appreciated.

Thanks!

[AngelosPachis]

Hi @krishnagandhi ,

You can group on employee name and number and summarize OT and RG Hours.

What's unclear to me is why in the desired output you have provided,

Monk, Lane has

occupies two records instead of one?

[krishnagandhi]

So Monk Lane has 2 records because you see in Job Step there is a C & D which are 2 different job titles so i need to keep them separate (downstream they have different rates).

I thought of that solution but my issue is it takes away the workdate field, which i need later on to compare against the swipe data for that particular date. So i need to keep the workdate for the non-nil hours

[AngelosPachis]

@krishnagandhi  From what I can see, job step is different for Biggar Correy, Irving Wilfred and Johnson Rydell as well, but you only kept the final, first and final job for each one of them.

The workdate can be brought back later on I think so it's not as important as getting the rows and numbers to match

[krishnagandhi]

The only reason the others are not kept are because those hours net to zero hours. Monk is the only individual that has 2 job titles that have hours attached to them. Even if we summarized them first, how would be bring back the work date later and split the hours accordingly between the dates?

Thanks!

[AngelosPachis · Accepted solution]

@krishnagandhi 

With a Join 🙂

Hope that helps,

Regards,

Angelos

[AngelosPachis]

To be honest, you have done pretty much everything. The point that was tricky was that you should not group by the work date.

Rather, summarize for everything else and you will see which jobs aggregate to 0 and which don't.

Then with the Join tool, if you connect the two streams, the jobs that add up to 0 will fall out of the L output anchor. Out of the R output anchor, you have all those jobs that have been aggregated to get that 0 figure for RG Hours and OT Hours.

Whatever joins is "outstanding" jobs

[krishnagandhi]

Wow, that was simpler than i thought! Thanks a lot! I was wracking my brain coming up with a solution to that one!