Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Splitting Dates into Rows

Highlighted
5 - Atom

Guys, I'm really stuck on a problem. I have two tables trying to be joined together. However the way they join depends on the dates of their dates. It is quite hard to explain so I show below. 

 

 Reporting Table    
KeyRepValid FromValid ToFrequencyFrequency Unit
ABC10 31/05/200610
ABC1001/06/200630/06/200610
ABC1001/07/200628/02/200710
ABC1001/03/2007 20

 

 Joining Table   
KeyRepSTValid FromValid To
ABC103001/07/200631/12/2006
ABC103001/01/200731/01/2007
ABC103001/02/200731/03/2007
ABC103001/04/2007 

 

I want the output to be this. 

 

KeyRepSTValid FromValid ToFrequencyFrequency Unit
ABC103001/07/200631/12/200610
ABC103001/01/200731/01/200710
ABC103001/02/200728/02/200710
ABC103001/03/200731/03/200720
ABC103001/04/2007 20

 

The dates on the joining table take precedence hence why all dates before 1st of July 2006 in Reporting Table weren't factored in the output. The idea is that the dates will not allign and if so there will need to be a split in the dataset accordingly. I hope I explained myself well enough! I've been stuck on this for a few days..

Highlighted
ACE Emeritus
ACE Emeritus

This is either a pretty complex problem that requires a decent amount of work to get right, or I'm missing a really simple solution to the problem.


I've attached an example workflow which works for your use case, and which I think should always work.  However, I would recommend testing it on a larger dataset to make sure it continues working.


I tried to document the flow so that the logic I followed to get to the end result is at least written out.  Hopefully this either solves your need, or gives you some ideas as to how to accomplish what you want in Alteryx.

If someone else suggests a simpler solution, I'd be excited to see it, as I think I may have overcomplicated things in my head, especially since my current solution uses 32 tools.

Highlighted
5 - Atom

Claje thanks for your reply it gave me some idea of what I should be doing. Your solution works in most cases however for my combined dataset, it does not seem to be working. This is more of my full dataset.

 

 Reporting Frequency Table    
KeyRepValid FromValid ToFrequencyFrequency Unit
ABC10 31/05/200610
ABC1001/06/200630/06/200610
ABC1001/07/200628/02/200710
ABC1001/03/2007 20
 Joining Table   
KeyRepSTValid FromValid To
ABC1010  
ABC102001/06/2006 
ABC103001/07/200631/12/2006
ABC103001/01/200731/01/2007
ABC103001/02/200731/03/2007
ABC103001/04/2007 

 

And the desired output should be

RE KeyRepSTValid FromValid ToFrequencyFrequency Unit
ABC1010 31/05/200610
ABC101001/06/200630/06/200610
ABC101001/07/200628/02/200710
ABC101001/03/2007 20
ABC102001/06/200630/06/200610
ABC102001/07/200628/02/200710
ABC102001/03/2007 20
ABC103001/07/200631/12/200610
ABC103001/01/200731/01/200710
ABC103001/02/200728/02/200710
ABC103001/03/200731/03/200720
ABC103001/04/2007 20

I've edited some of your workflow but am really struggling to get this full output. I noticed that if I grouped the joining table by Key, Rep and ST and then ran them they individually gave the right output but not when grouped together because the dates get combined. To solve this I attempted a batch macro but am really struggling. My batch macro does not seem to be getting the desired output. Also thank you so much Claje for the time and effort you put in :) 

Labels