Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mrooney
Contributor III
Contributor III

Geting the previous Item Price using Bottom()

Hi there,

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

My inventory movements table looks like this

       

Wharehouse ItemCodeYearMonthDayQtyAvg. Price
W132332015115101.5
W232332015130181.35
W33233201525-101.35
W432332015218151.32
W532332015229251.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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
mrooney
Contributor III
Contributor III
Author

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.


talk is cheap, supply exceeds demand