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

[DavidP]

The problem you have with the 3 data sets is that there are multiple rows for each employee in each of the data sets, so you have to find a way to match the right rows together, otherwise you will get duplicates, as you have found.

Can you share how you have tried to join them.

[HBarge]

Hi @DavidP 

Sorry for the embarrassing attempt however, here is my futile progress (Attempt) so far.

Attempt

[DavidP]

Never apologize or feel embarrassed about your workflows - we might all approach a problem in our own way, but it doesn't mean one method is better than another. That's the beauty of Alteryx.

It looks like the problem lies in the clockin data - there are 1067 records where the Emp Code and Clockin Date combination produces a duplicate. The join tools relies on this relationship being unique.

So one option would be to use a summarize tool. I've modified your workflow slightly - have a look at how I've configured the Summarize tool that I inserted to deal with the duplicates. You may want to adjust some of the selections.

[HBarge · Accepted solution]

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