We have a requirement to join the hiring information from two HR data sets. The only possible key would be a combination of employee_id and hiring date which I call %HiringKey. This approach works for 85% of the cases. But for 15%, the hiring date in both systems vary by 1-2 days since the source teams for these systems operate and report separately and there would sometimes be a delay in entering the information to the systems.
Let me take an example to explain such a scenario:
In data set A, we have employee_Id=1234 and hiring date as 07/01/2015. So the key generated would be 1234-07/01/2015
In date set B, we have the same transaction for employee id 1234 but the hiring date is slightly different as 07/02/2015. The key thus generated would be 1234-07/02/2015. And since these keys doesn't match, the required association is lost for this record between the systems.
Is there anything that we can do at qlikview side so that an association is made if the dates at either side falls within a range plus or minus 10 days. Because, the scenario where same employee having hired twice within a 20 day period was ruled out by the business team.
Re: Association based on a range of values possible ?
I found the solution by Jonathan most fitting in my scenario.
When I did a employee_Id & '-' & Date(Round([hiring date], 20)), there were still a couple of similar dates which fell in different 20' bucket and missed the join. Since I was using this in an applymap, in the null condition parameter I included one more similar join, this time with a range of 10.So the missed out joins in the first attempt made sure to join correctly in the second.