Geting the previous Item Price using Bottom()

[mrooney]

Hi there,

Mi client asked me to have his inventory valued at any give date.

My inventory movements table looks like this

Wharehouse	ItemCode	Year	Month	Day	Qty	Avg. Price
W1	3233	2015	1	15	10	1.5
W2	3233	2015	1	30	18	1.35
W3	3233	2015	2	5	-10	1.35
W4	3233	2015	2	18	15	1.32
W5	3233	2015	2	29	25	1.51

So, in case there is no movement at a particular date, I have to take the Avg. Price from de first previous movement.

My approach:

1.- I haven't solved it in script because preformance issues could appear (lots of warehouses, Items and too much gaps beetwen movement dates).

2.- I'm using (and it's working fine) this expression (Simplified):

               Sum(Qty) * Bottom(Aggr(Sum(AvgPrice),Warehouse,ItemCode,Year,Month,Day))

The question:

¿Do you think there is a better solution than this one in order to improve performance?

Thanks for your help guys.

Regards.

[Gysbert_Wassenaar · Accepted solution]

I'd still fix it in the script. It's easier and will result in better performance of the front end.

Temp:

LOAD * FROM ...source... ;

Result:

NOCONCATENATE LOAD

     Wharehouse as Warehouse,

     ItemCode,

     Year,

     Month,

     Day,

     Qty,

     alt([Avg. Price], if(previous(ItemCode)=ItemCode, peek('Avg. Price'))) as [Avg. Price]

RESIDENT Temp

ORDER BY ItemCode, Year, Month, Day;

DROP TABLE Temp;

[mrooney]

Hi Gysbert,

Thanks for your reply.

Do you think it would work better even with more tha 30 million rows? (Sorry I'm not very good at testing performance)

Regards.

[Gysbert_Wassenaar]

Yes. Millions of rows will hurt performance much worse in the front end.