Cartesian Product

I’ve got a frequent issue in inventory management.
I have only data for specific days (colored cells)

Based on a calendar table, I want a line for each day and for each part number (cartesian product)
Then, I would like to perform the following calculations:

  • stock evening* (day) = stock morning (day) - Sales (day) trivial
  • stock morning (day +1) = Stock evening (day) row calculation
  • A date for each day of the calendar cartesian product
    Inventory Calculation.xlsx (14.7 KB)

Do you think it is possible with EM?

Best Regards
Michel

It looks like in your example it’s not really a Cartesian product because dates

02/01/2017
03/01/2017
05/01/2017

are missing for Part Number “A”.

I assume you still need a Cartesian product here. The idiomatic way for obtaining it is adding a dummy column in each dataset and then do left/full join on that dummy column.

To interpolate stock numbers for the new rows use Fill Down and Shift columns. See the project below:

stock interpolation.morph (6.8 KB)

See also: Generating Multiple Rows with one Row

That is simply brilliant, Dmitry…
A lot of good ideas to reuse!
And a very powerful solution for leverage the data from ERP
Thanks!
Michel

You’re welcome, Michel! :slight_smile: