Need help replicating a Between Join in Alteryx

Hello,

I have an ask for pulling two different data sources together and joining on fields. This used to be done with a SQL query but now the data sources are on different servers and database systems, so I figured alteryx would be best to get this work done. Here are the joins I need to replicate in Alteryx:

FULL JOIN TABLE1

ON ORDER_TYPE = Ord_Type
AND Alloc_Day BETWEEN WEEK_START AND WEEK_END

I found some posts about conditional join, using the join tool and union, but that doesn't seem to be working for me. I'm just seeing triple the data that I should be seeing (meaning the join isn't working correctly). Any idea how I can accomplish such a join?

Join

Accepted answers

atcodedog05

Hi @uahmed90

Here is my take.

Workflow:

If input dates is not in format

If input dates in format

Hope this helps : )

between dates (dates not in format).yxmd

between dates (dates in format).yxmd

uahmed90

Thank you! This was the closest solution, I would've never thought to go this route but it actually worked somehow. Thanks!

All comments

atcodedog05

Hi @uahmed90

Can you provide some sample input and expected output It will help us get a better understanding of the usecase.

We will be happy to help : )

uahmed90

TABLE 1 TABLE 2

ALLOC_DAY	ORD_TYPE	PKT_QTY	WEEK_START	WEEK_END	WEEK_NUM	GOAL_QTY	ORDER_TYPE
12/1/2021	FUTURES	8904	12/1/2021	12/5/2021	1	56087	FUTURES
12/6/2021	FUTURES	6146	12/6/2021	12/12/2021	2	48217	FUTURES
12/13/2021	FUTURES	11081	12/13/2021	12/19/2021	3	0	FUTURES
12/20/2021	FUTURES	17982	12/20/2021	12/26/2021	4	0	FUTURES
12/27/2021	FUTURES	1679	12/27/2021	12/31/2021	5	0	FUTURES

See above table. What I need to do is join on the order type, and also grab the Actual Qty between the week_start and week_end date. So the DAY date can be any day, but I need to sum what was created between the week_start and week_end. Hope that makes sense.

uahmed90

RESULT
ALLOC_DAY	ORD_TYPE	ACTL_QTY	GOAL_QTY	WEEK_START	WEEK_END
12/1/2021	FUTURES	8904	56087	12/1/2021	12/5/2021
12/6/2021	FUTURES	6146	48217	12/6/2021	12/12/2021
12/13/2021	FUTURES	11081	0	12/13/2021	12/19/2021
12/20/2021	FUTURES	17982	0	12/20/2021	12/26/2021
12/27/2021	FUTURES	1679	0	12/27/2021	12/31/2021