Intervals: inventory balance in time

[#1]

Hello community, I've come across a situation that's been giving me some trouble the whole weekend... I think I'm at the point where I need some help:

I've got this table with the inventory records for each material and the interval of time where that inventory balance was valid.

MatID	Stock	Start	End
A	1	01/08/2015	07/08/2015
A	3	08/08/2015	13/08/2015
A	5	14/08/2015	24/08/2015
A	7	25/08/2015	25/08/2015
B	2	01/08/2015	07/08/2015
B	4	08/08/2015	15/08/2015
B	6	16/08/2015	20/08/2015
B	8	21/08/2015	25/08/2015

The intervals for each inventory record for each material may not be the same but there are not duplicates and each of them has both an start and an end date.

How could I combine this with a calendar table so that I am able, for example, to calculate the total inventory balance on 09/08/2015 ( 3 + 4 )?

Thanks in advance!

[MK_QSL · Accepted solution]

temp:
Load * Inline
[
  MatID, Stock, Start, End
  A, 1, 01/08/2015, 07/08/2015
  A, 3, 08/08/2015, 13/08/2015
  A, 5, 14/08/2015, 24/08/2015
  A, 7, 25/08/2015, 25/08/2015
  B, 2, 01/08/2015, 07/08/2015
  B, 4, 08/08/2015, 15/08/2015
  B, 6, 16/08/2015, 20/08/2015
  B, 8, 21/08/2015, 25/08/2015
];


NoConcatenate
Load
  MatID,
  Stock,
  Start,
  End,
  Date(Start + IterNo() - 1) as Date
Resident temp
while Start + IterNo() - 1 <= End;


Drop Table temp;

Now create a straight table

Dimension

Date

Expression

SUM(Stock)

[#3]

Wow, that was fast.

Thank you very much!