How to auto-fill in empty cells with first value til next value etc?

[#1]

Hi Experts,

Can you please recommend a fastest (performance wise) solution on how to deal with the following.

I have this table with empty/null fileds - please see below -  but I need that instead of those empty fields/nulls in Price column,  the first Price  column was filled in with the Price Value of the first Price Value till the next Price Value. Then after the next Price Value it was filled with the next Price Value til the next next Price Value etc..... 

So it would look like this:

Time               Price

03:16:56          4604

03:16:56          4603.75

03:16:57          4603.75

03:17:01          4603.75

.......

03:17:10          4604

03:17:13          4604

03:17:26          4603.75

03:17:30          4603.75

.......

etc.....

Any idea? Thanks in advance.

kind regards

r.

P.S. there is no any logic /patterns on how the Price changes..... 

[swuehl]

T1:

LOAD time, price, quantity

FROM YourTable;

RESULT:

NOCONCATENATE

LOAD time,

          alt(price, Peek(price) ) as price,

          quantity

RESIDENT T1

ORDER BY time;

DROP TABLE T1;

edit: changed previous() to peek()

[sunny_talwar]

Are you trying to do this in an expression or back end of the script?

[#4]

@SunnyT  I would prefere it to be done in backend / the script editor if its possible (when reloading the script once). Basically I got a table from MS SQL DB.

mytable:

SQL SELECT  "Price",

    date,

    "Volume_size"

FROM "my_db".dbo."my_table";

[sunny_talwar]

Then I think you should be able to do it using swuehl‌'s provided script. If it did not work, then provide a sample (preferably in an Excel file) to help you better.

[swuehl]

Try something like

mytable:

LOAD Alt( Price, Peek( Price) ) as Price,

          date.

          [Volume_size];

SQL SELECT  "Price",

    date,

    "Volume_size"

FROM "my_db".dbo."my_table"

ORDER BY date ASC;

[#7]

Hi, I tryed the script it did solved but partially. i am still having blanks. I am attaching the main table - got exported into csv.

I made a straight table with dimentions time, price and expresion sum(Quantity). After using swuel script it looks like this.

[Anonymous]

This blog post Generating Missing Data In QlikView by HIC is well worth a read.  Not just re your question, but also other related scenarios.

[swuehl · Accepted solution]

This should fill in prices in your sample csv:

SET ThousandSep=',';
SET DecimalSep='.';


T1:
LOAD Ask_price,
    Ask_size,
    Bid_price,
    Bid_size,
    date,
    Price,
    Quantity,
    time,
    Volume_size
FROM
[sample (1).txt]
(txt, utf8, embedded labels, delimiter is ';', msq);




LOAD recno() as RecNo,
  date,
  time,
  if(len(trim(Price)),Price,Peek(Price)) as Price,
  Quantity
RESIDENT T1
ORDER BY date, time;


DROP TABLE T1;