Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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..... 

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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

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;

8 Replies
MVP
MVP

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

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?

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

Not applicable

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

@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?

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.

MVP
MVP

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

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;

Not applicable

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

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?

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.

MVP
MVP

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

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;

Community Browser