Join Two Sets of Date

[hyan028]

Hi,

I have two separate date field like this:

A

6/30/2014

9/30/2014

12/31/2014

3/31/2015

6/30/2015

B

10/26/2014

1/15/2015

Then I want to join the two based on whether the B date is within the range of A.  So the result should be like:

A                             B

6/30/2014

9/30/2014

12/31/2014          10/26/2014

3/31/2015             1/15/2015

6/30/2015

How can I make this happen????

Best,

Blake

[kat]

Hi @hyan028

Started to build it for you, but was then wondering, what would happen if more than one date fits in a range?

Cheers

Kat

[hyan028]

Hey @kat,

Thanks for the question.  I thought about it too.  Based on my understanding of the nature of my data, it shouldn't happen.  So it will be only one date fits in a range.

Best,

Blake

[kat · Accepted solution]

Hi @hyan028

Here's a solution for you.

hope this helps

Kat

[BenMoss · Accepted solution]

There is a weekly challenge that would definitely help with your learning of this problem, the 'join to range' problem, although slightly different would allow you to understand how this could work...

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-1-Join-to-Range/td-p/36621

Personally I would approach this in a different way to @kat which is to use the generate rows.

Example attached.

Ben

[hyan028]

Thanks @kat, definitely works.  I'll look into my data and decide which version to use to best flow my work but no doubt it is a solution!

[hyan028]

Thanks @BenMoss, it solves the problem too. I'm still weighing the option on embedding which version to my current workflow, but the solution is great.