Need help with Reconciliation of data to a flat file

HBarge

Hi Community,

I have 3 datasets; Namely

1. Employee Clockin Data
2. Employee Payroll Data
3. Employee Award Data
   (attached)

I want to reconcile this dataset into one flat file.

Where I am getting wrong is when I join and union this dataset, Payroll numbers are getting duplicated and thus amplified (sum).

The expected flat file is in the below format:

Employee Number

Name

Date

Award Level

Min_Position From

Max_Position To

Award Year

Activity Codes

Start Date Time

Stop Date Time

Unadjusted Shift Length (Hours)

Start Day

Finish Day

Carry Over Next Day

Start Day Weekend

Finish Day Weekend

Period End Date

Sum_Taxable Income

I have tried various methods, but I am unable to get the data what I want.

Appreciate the help and direction.

You can find files here (backup)

Thanks,

Harshad

Award Data.csv

Clockin Data.csv

Payroll Data.csv

HBarge

Hi @DavidP 

Yours is the most elegant solution. Thank you for that. Learnt new stuff about Summarize tool

However, for this specific example, I realized, I was following the incorrect sequence of steps. I worked on it a bit more and got the result I wanted.

I created a master of the employee list as I was to bring in everything wrt Date and the Employee list and then further followed the chain of steps.

Again, Thank you sir!

Please find the solution. Feedback is much appreciated!

Thank you again.

Harshad

Attempt 2.yxzp