Running total processing journal entries

One Star

Running total processing journal entries

Hi all

I want to create a job which takes a current stock level for a product in a warehouse/location (in an inventory management system), journal entries describing stock moving into or out of a location (which have a date, location, product number and a quantity on them) and from there, work backwards in time to give a monthly stock level for each product in each location.

I have got to the point where I have a list of location/product/dates (which I generated from a calendar dimension tmapped to my stock locations table and my products table) and I have another record set of my current stock levels/stock movements.

What I cannot figure out how to do is to keep a "running" total, from date to date, going back in time.

e.g.

For warehouse/stock location 'ABC1' and product number 'A0001', current stock (today - 21/2/2011) is 1000. I have a record dated 10th January 2011 for this product/warehouse which moves 100 into this warehouse (so before that date, the stock would have been 900). And another record dated 12th November 2010 which moves 200 in (so before this date the stock was 700).

I would like to run a job which, against my list of location/product/dates, can keep a running total and add this to the appropriate record. So my "end of January 2011 record" would have 1000 in it, my "end of December 2010" record would have 900 in it, my "end of November record 2010" would have 900 in it and my "end of October 2010" record would have 700 in it.

Does that make sense?

Any help much appreciated!

Kind regards,

Matthew

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog