How to copy previous day data?

[nboccassini]

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

[marcus_sommer]

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

[ecolomer]

Use function ABOVE or BEFORE

[sathishkumar_go]

HI,

Try this,

load

Place,

Category,

Date,

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

Price

from table;

-Sathish