Need help with Reconciliation of data to a flat file
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Employee Clockin Data
- Employee Payroll Data
- Employee Award Data
(attached)
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 |
Solved! Go to Solution.
- Labels:
- Data Investigation
- Preparation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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