Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Need help replicating a Between Join in Alteryx

uahmed90
7 - Meteor

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
22 - Nova
22 - Nova

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
7 - Meteor

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
7 - Meteor
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
20 - Arcturus
20 - Arcturus

@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
22 - Nova
22 - Nova

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
7 - Meteor

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

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @uahmed90 

Cheers and have a nice day!

Labels