Converting a complex excel SUMIFS (multiple criteria and I need to retain the zeroes)

[enwiegand]

I have an excel file I'd like to bring into alteryx. 

The master file has 2 data sources, sales data and production data.

All of the items sold must be associated with a production log. 

Once produced, the items can be sold for 3 days. 

For a given date of sales, the excel file sums the number of items produced (according to the production log), if the location, item number, and [date sold, date sold - 1, date sold - 2] are ALL a match. This formula is in column E of the "match table" tab. 

Because the items can be sold for 3 days, one day of production log may cover several days of sales. 

For a given date of sales, if there is no associated production log, the formula returns zero, which is how I am filtering locations that are non-compliant. 

As far as I can tell, the join multiple tool doesn't retain the rows of sales data for which there is no matching production data. Additionally, because the date sold is not a one to one match with the production date, some data is being excluded. 

I was thinking I could generate rows in the production data (just create new rows for each line of production data that is [date produced +1 and date produced +2]), But I can't figure out how to make that work either. 

[jdunkerley79]

I suggest something like this:

First join the PLU and Location data to the sales data. This does causes the sales data order to be shuffled. I added a couple of tests tools to check that the join always work.

After this I create a Key to use as an index on the Production record of <Date><PLU><Location>

The Sales data then creates 3 keys in this format (one for sales date and the two preceding days).

Using a Find and Replace tool will then join the production data 3 times without loss of sales record.

Finally some formulas to compute the total watching for nulls from the find and replace.

Have attached the quick example

Hope it helps.

[DavidP · Accepted solution]

Hi @enwiegand 

Here's how I'd do it.

[enwiegand]

I like this solution, very elegant, what I had in mind! <deleted this, user error!>