Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

If Date Lies in Between Join

harshal98
8 - Asteroid

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

 

Input1Input1Input2Input2OutputOutput

4 REPLIES 4
Qiu
20 - Arcturus
20 - Arcturus

@harshal98 
What do you want to do with overlapping,

Say Emp id 1 for 3rd row, the manager can be a or c?

Qiu_0-1608621452530.png

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@harshal98 ,

 

 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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
AngelosPachis
16 - Nebula

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

 

AngelosPachis_0-1608622394374.png

 

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

pedrodrfaria
13 - Pulsar

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)

 

pedrodrfaria_0-1608640737784.png

 

Pedro.

 

Labels