For each Employee, Check which Effective Date from Input 1 Lies in between Start and End Date in Input 2 .... and join manager accordingly in Input 1
@harshal98
What do you want to do with overlapping,
Say Emp id 1 for 3rd row, the manager can be a or c?
there's a CReW macro to generate records between date ranges for the ease of joining. You can then use a simple join.
https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14 https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14
cheers,
mark
Hi @harshal98 ,
There is this Crew macro that @MarqueeCrew mentioned above that can help you easily generate the missing rows.
If you don't want to go down the macro route, you can use a generate rows tool instead, and this is what your workflow should look like
You have to generate all dates between your starts and end date and then join them back to you Input 1 where you have your employee records. Then you would need a union tool, to bring back employee records that don't get joined and fall out of the L output anchor.
Hope that helps let me know if you have any questions.
Regards,
Angelos
Hi @harshal98
I've attached the workflow below to display how you can create the range and join in the manager. You can add the union afterwards and union the different outputs if you want all of them together, or some of them (ex: Left Output and Join Output)
Pedro.