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

How to look up values based on dates in a date range across multiple datasets?

Matt91
5 - Atom

I have two data streams, one showing cost price history, the other showing retail price history.

 

I want to create a single table that shows at a given point in time (the last day of a fiscal period in this case) what was the cost and retail price of a given product.

 

I'm not sure of the best way to do this, would it be to create an iterative macro that checks whether Fiscal Period Data. Last Date >= Start Date and <= EndDate?

 

There are hundreds of thousands of price change records and over 60k SKUs so need something relatively efficient performance wise.

 

Cost Price Data

Matt91_0-1653498032130.png

 

Retail Price Data

Matt91_1-1653498072158.png

 

Fiscal Period Data

Matt91_3-1653498292454.png

 

 

Desired Output

Matt91_4-1653498423019.png

 

2 REPLIES 2
Luke_C
17 - Castor

Hi @Matt91 

 

Have you tried the generate rows tool? Basically generate a row for each day in the start/end date range, and join up to the period data. Your data appears to have gaps/doesn't make sense. For instance there are pricing records with a start date that is after the end date. 

 

Hopefully this gets you on the right track.

Luke_C_0-1653514128168.png

 

 

Matt91
5 - Atom

Hi @Luke_C,

 

Thanks this absolutely got me on the right track. Data gaps were to do with how I'd generated the sample data.

 

I added a couple of steps to clean up the data and a multi-row formula to replace null values.  Workflow attached, thanks for your help!

 

Matt91_0-1653571609843.png

 

 

Labels