community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Conditional join from a lookup table with conditions & overlapping criteria

Atom

Hi,

 

I am trying to bring in sales data to a inventory file for calculating inv. turns.(pls see below for table formats). 

 

So. in inv table i have period (month & year) and I want to bring in the last 12 month sales from the sales file to calculate the turns.(ie. for Jan 2019, I need to sum up from Jan to Dec 2018 sales) Challenge here is same row in sales data will come under multiple periods. I am having both the tables in .xlsx format.

 

Inv. table Format

 

Month Year Inv. Value ($) Demand (to be looked up from sales table)
jan201810 
feb201815 
mar201810 
apr201815 
may201810 
jun201830 
jul201810 
aug201815 
sep201810 
oct201815 
nov201810 
dec201815 

 

Sales table format

 

Month Year Sales ($)
jan20174
feb20174
mar20175
apr20175
may20176
jun20177
jul20174
aug20174
sep20175
oct20175
nov20176
dec20177
jan20187
feb20184
mar20185
apr20185
may20186
jun20187
jul20187
aug20184
sep20185
oct20185
nov20186
dec20187

 

Any help in this would be highly appreciated.

 

Regards

Arackal

Asteroid

Hi arackal,

 

It's not 100% clear for me. Do you need the 2017's sales data or not?

 

If not, then you can use a simple join tool with two criteria, see the attached pict. I hope it can helps you Let me know if i'm wrong and we will try to figure out something else.

Best,

Kucsi

Kucsi_0-1574678639478.png

 

Atom

Hi Kucsi,

 

Thanks for replying.

 

So, how I want to map is for every month in inv. file, I want the sum of sales in the last 12 months from the demand file.

 

Ex- Against Jan 2018 row in inv. table, I want sum of (Jan 2017 - Dec 2017) from the demand file. 

 

Hope this makes my situation clear. Any thoughts on how to solve this ?

Alteryx Certified Partner
Alteryx Certified Partner

Hi @arackal ,

 

you can use a Multi-Row Formula to add up the last 12 months of the sales table and join the result to the inventory table. I've created a sample workflow and added some sorting to ensure the right order of the sales figures. Hope this is helpful.

 

Best regards

 

Roland

Bolide

Hi @arackal 

 

Here is another way to achieve what you are trying to do.

 

I hope this helps.

 

arackal_query.jpg

Atom

@schubert thanks a lot for helping out! this works!

Bolide

.

Atom

@ Dazzerman,  got it...even I couldn't find your earlier post..thanks for sharing..good to be aware of this method and will come handy for similar moving sum type calculations...thanks!!!

Bolide

@arackal 

 

No idea what happened there!  It has reappeared again, so that is very strange!

 

At least you've got it, so all good.  Cheers!

Asteroid

I see you got a solution glad to hear it!

 

 

Labels