Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Need help replicating a Between Join in Alteryx

uahmed90
6 - Meteoroid

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?

7 REPLIES 7
atcodedog05
21 - Polaris

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
6 - Meteoroid

TABLE 1                                                                                                           TABLE 2

ALLOC_DAYORD_TYPEPKT_QTY WEEK_STARTWEEK_ENDWEEK_NUMGOAL_QTYORDER_TYPE
12/1/2021FUTURES8904 12/1/202112/5/2021156087FUTURES
12/6/2021FUTURES6146 12/6/202112/12/2021248217FUTURES
12/13/2021FUTURES11081 12/13/202112/19/202130FUTURES
12/20/2021FUTURES17982 12/20/202112/26/202140FUTURES
12/27/2021FUTURES1679 12/27/202112/31/202150FUTURES

 

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
6 - Meteoroid
RESULT
ALLOC_DAYORD_TYPEACTL_QTYGOAL_QTYWEEK_STARTWEEK_END
12/1/2021FUTURES89045608712/1/202112/5/2021
12/6/2021FUTURES61464821712/6/202112/12/2021
12/13/2021FUTURES11081012/13/202112/19/2021
12/20/2021FUTURES17982012/20/202112/26/2021
12/27/2021FUTURES1679012/27/202112/31/2021

 

Result should be something like this.

Qiu
18 - Pollux

@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.

0115-uahmed90.PNG

atcodedog05
21 - Polaris

Hi @uahmed90 

 

Here is my take.

 

Workflow:

 

If input dates is not in format

atcodedog05_0-1642226892564.png

 

If input dates in format

atcodedog05_0-1642227075120.png

 

 

Hope this helps : )

uahmed90
6 - Meteoroid

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

atcodedog05
21 - Polaris

Happy to help : ) @uahmed90 

Cheers and have a nice day!

Labels