Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
mrooney
New 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
MVP & Luminary
MVP & Luminary

Re: Geting the previous Item Price using Bottom()

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
MVP & Luminary
MVP & Luminary

Re: Geting the previous Item Price using Bottom()

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

mrooney
New Contributor III

Re: Geting the previous Item Price using Bottom()

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.

MVP & Luminary
MVP & Luminary

Re: Geting the previous Item Price using Bottom()

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


talk is cheap, supply exceeds demand