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

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.

dummydata.xlsx

Help

Data Investigation

Accepted answers

DavidP

Hi @enwiegand

Here's how I'd do it.

match sales and production records.yxmd

All comments

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.

Match Sales and Production.yxmd

DavidP

Hi @enwiegand

Here's how I'd do it.

match sales and production records.yxmd

enwiegand

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703