topic Re: Geting the previous Item Price using Bottom() in QlikView

Geting the previous Item Price using Bottom()

mrooney — Fri, 27 Mar 2015 14:29:34 GMT

Hi there,

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

My inventory movements table looks like this

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 — Fri, 27 Mar 2015 14:50:47 GMT

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 — Fri, 27 Mar 2015 14:58:18 GMT

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 — Fri, 27 Mar 2015 15:06:35 GMT

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