topic How to copy previous day data? in QlikView

How to copy previous day data?

nboccassini — Fri, 19 Sep 2014 18:50:20 GMT

Hi, I have a big table (36 million rows) like this (the real table has 4 different price columns):

Place	Category	Date(dd/mm/yyyy)	Price
A	1	01/01/2014	10
A	1	02/01/2014
A	1	03/01/2014	8
...	...	...	...
A	2	01/01/2014	5
A	2	02/01/2014
A	2	03/01/2014
B	....	...

I want that the places without price (for a category) have the last price:. For example

Place	Category	Date(dd/mm/yyyy)	Price
A	1	01/01/2014	10
A	1	02/01/2014	10
A	1	03/01/2014	8
...	...	...	...
A	2	01/01/2014	5
A	2	02/01/2014	5
A	2	03/01/2014	5
B	...	...	...

Now I use the peek function for copy the last price, but the the reload is more slow.

Is there a faster way?

Thanks

Re: How to copy previous day data?

marcus_sommer — Sat, 20 Sep 2014 09:38:52 GMT

You could use an aggregation like this:

lastprice:

Load Place, max(Date) as MaxDate, Price From xyz Group by Place, Price;

But better for performance by 36M rows is to use incremental loads: Incremental Load. Also it could be that's more and/or enough performant to use firstsortedvalue() within the gui instead to calculate it in the script.

- Marcus

Re: How to copy previous day data?

ecolomer — Sat, 20 Sep 2014 16:27:46 GMT

Use function ABOVE or BEFORE

Re: How to copy previous day data?

sathishkumar_go — Mon, 22 Sep 2014 13:44:09 GMT

HI,

Try this,

load

Place,

Category,

Date,

if(isnull(Price),peek(Price),Price) as New_Price,

Price

from table;

-Sathish