It can be done in the load script by looping through each date, summing the data and concatenating the sum to a new table.
In more detail:
1 Create a new table of distinct dates in the data (or use a calendar script to generate all dates between min and max dates) eg
DateList:
Load Distinct Date
Resident Data;
2 Create a new result table with no rows eg
Results:
Load Total
Autgenerate (0);
3 Loop through the dates eg
for i = 0 to noofrows(DateList)-1
let vLoopDate = peek('Date', $(i), 'DateList')
Concatenate (Results)
Load vLoopDate as Date
Sum[Change in Stock] as Total
Resident Data
Where Date >= vLoopDate;
Next
Comments
The date comparison in the Where clause may need tweaking, depending on your date format.
If you need to do the same per product code for instance then add a group by clause to the concatenate statement. Not that this will make an inefficient loop even more inefficient!
