Help Please~total cubic feet for all the boxes in a period

[#1]

CompletedCubicMeasure:
load
OrderNo,
(sum(Width) * sum(Length) * sum(Height))/1728 as 'Cubic Space'  //row 1
resident ProcessingCubicMeasure group by OrderNo;

drop table ProcessingCubicMeasure;

for this code, what i want to do is to show the % of cubic feet our truck had loaded in any given period.

basically, if there are 4 boxes in one day, and each of them is 1 x 1 x 1, then the total cubic feet for the day should be (1+1+1+1) x (1+1+1+1) x (1+1+1+1).

help thanks!

[#2]

To measure total cubic feet (with L, W, and H in feet):

Specs:

LOAD * INLINE [
    OrderNo, Width, Length, Height
    123, 1, 1, 1
    234, 2, 3, 4
    567, 5, 1, 3
    898, 4, 5, 6
    156, 3, 4, 4
    785, 4, 4, 4
];


LEFT JOIN
LOAD OrderNo,
Width * Length * Height as CubicSpace
Resident Specs;    

 

Then you could just sum the Cubic Space to find Total Cubic Space and divide the Total Cubic Space by the Total Available Space to find the % of Space Used.  Is this what you are looking for?

[#3]

hi Rebecca,

     I cannot use the inline function because im pulling the data straight from the data stored on our server, so does that mean:

CompletedCubicMeasure:
load
OrderNo,
(sum(Width) * sum(Length) * sum(Height))/1728 as 'Cubic Space'                                     //row 1
resident ProcessingCubicMeasure group by OrderNo;

drop table ProcessingCubicMeasure;

is correct???

[#4 · Accepted solution]

The inline load was just an example, you would still use the data stored on your server as is.  It looks like this is what you want:

CompletedCubicMeasure:
Load OrderNo,
(Width * Length *Height)/1728 as [Cubic Space]

Resident ProcessingCubicMeasure;

Drop Table ProcessingCubicMeasure;

Then you will be able to use Sum([Cubic Space]) as your Total Cubic Space and you can divide your Total Cubic Space by your Available Cubic Space to get your %.

[christian77]

Hi:

If you can't do it in the script, as they've recomended, try sum(aggr(Width * Length * Height, BoxNo, OrderNo)).

[#6]

thank you so much Rebecca~