Need help replicating a Between Join in Alteryx

[uahmed90]

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?

[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

Result should be something like this.

[Qiu]

@uahmed90 
I hope this is what you need.
I added one sample tool also since there are maybe more than one ALLOC_DAY in a particular week.

[atcodedog05 · Accepted solution]

Hi @uahmed90 

Here is my take.

Workflow:

If input dates is not in format

If input dates in format

Hope this helps : )

[uahmed90 · Accepted solution]

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

[atcodedog05]

Happy to help : ) @uahmed90 

Cheers and have a nice day!