topic Re: How to auto-fill in empty cells with first value til next value etc? in QlikView

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

Mon, 28 Dec 2015 20:28:07 GMT

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

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

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

swuehl — Mon, 28 Dec 2015 20:53:27 GMT

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()

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

sunny_talwar — Mon, 28 Dec 2015 20:57:56 GMT

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

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

Mon, 28 Dec 2015 21:05:29 GMT

@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";

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

sunny_talwar — Mon, 28 Dec 2015 21:18:41 GMT

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.

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

swuehl — Mon, 28 Dec 2015 21:24:02 GMT

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;

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

Mon, 28 Dec 2015 21:58:24 GMT

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.

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

Anonymous — Mon, 28 Dec 2015 22:05:36 GMT

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.

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

swuehl — Mon, 28 Dec 2015 22:38:06 GMT

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;