Appending data to a datasource when a condition is met

[Kirstin]

Hi All,

I have 2 data sources:

1.  Historical sales (this is complete sales data per year for the last 5 years)

2.  Current year sales data that is updated each month.

I have one workflow that has reads in all the historical sales and saves those as an alteryx database.  I don't need to refresh those again as they don't change, so the workflow is only run once.   In another workflow I read in the most recent sales data for 2017 and then union it the historical sales.  This means I only refresh the data that is changing.  This will work fine until we reach January 2018, as the workflow reading in the historical data isn't run again, and the workflow updating the most recent data won't look at 2017. 

What I'd like to do is update my recent workflow so that once I get to January 2018, it updates the historical database to include 2017 and then continues to union the monthly updates for 2018 -  I just don't know how to do it.  Guessing it's a conditional join or something.

Ideas / push in the right direction would be great!

Thanks

Kirstin

[MarqueeCrew · Accepted solution]

Good morning @Kirstin,

I have 2 logical data sources:

1. Historical sales

2. Current year sales

I would consider that you have periodic sales that can be partitioned into annual sales.  If you were to save the files with their YEAR as part of the file name, you can have a directory containing 5, 10 or even more years of sales.  Sometimes you may have a desire to read all historic sales and sometimes you might want to read only a subset of the data.  I might consider using a Directory Tool to read the filenames into your workflow followed by a Filter tool to limit the read of data (when applicable).  This is followed by a Dynamic Input tool to actually bring the data into the process.  When you are ready to save your current year data, you can Filter the data based upon the year and output only that data where:

Left([Sales_Date],4) == Left(DateTimeStart(),4)

Then output the data to your sales data directory as:  Annual_Sales_YYYY.yxdb where you modify the output with the YYYY as either an append or filename replace.

You'll never have to worry about January updates to a new filename again.

You asked for ideas or a push in the right direction and this is my idea.  Hopefully it pushes you into the right direction.

Cheers,

Mark

[Kirstin]

Thanks :-)