replace null values

[#1]

I have a problem with a table ( see image ) : I want to replace the null values for the field PREZZOLISTINOL with the last not null value. For instance, looking at the image for the dates from 16/10/2011 to 25/10/2011 the field PREZZOLISTINO should contain the value 0,335.

I want to do this in the script of QlikView.

Thanks you in advance!

[swuehl]

Use peek() function in combination with isnull(FIELD) [or len(trim(FIELD))=0 if you want to catch also the blank values] in your script, something like

LOAD

..

if(len(trim(PREZZOLISTINOL))=0,peek(PREZZOLISTINOL),PREZZOLISTINOL) as PREZZOLISTINOL,

...

from Table;

[#3]

This should do it from the script.

On a table "resident" using the function "peek"

Saludos

Paco

[giakoum]

Please see attached.

[Final]:

LOAD

          A,

          If(IsNull(B), Peek('B'), B) as B

FROM

C:\Users\giakouma\Desktop\1.xlsx

(ooxml, no labels, table is Sheet1);

Sorry guys, was on edit mode and did not realize that you replied already.

[#5]

Thanks for your suggestions, but these solutions don't work for me. I saw that the peek function takes a string as field name, while my field is numeric ( it is a price ). 

If I run this code I do not get any results.

Tab1:

NoConcatenate

LOAD
DATAPREZZOLISTINOL as ddd,
CATPRODT113,
if(isnull(PREZZOLISTINOL), peek(PREZZOLISTINOL) ,PREZZOLISTINOL) as prezznew
resident TabellaPrezzoListino;

Plus I should in some way "group by" the field CATPRODT113 to get the right result, but if I run this code I get an error:

"Aggregation expressions required by GROUP BY clause"

Tab1:

NoConcatenate

LOAD
DATAPREZZOLISTINOL as ddd,
CATPRODT113,
if(isnull(PREZZOLISTINOL), peek(PREZZOLISTINOL) ,PREZZOLISTINOL) as prezznew
resident TabellaPrezzoListino Group By CATPRODT113;

Thanks again your advance!

[swuehl]

The format of your field shouldn't matter, you are giving the field name, not the field values as argument.

A group by does indeed need aggregation functions around all input fields used but not listed in your group by clause. But, maybe you just want to order by, instead of group by?

That's just guessing, could you upload a small sample togehter with your expected results?

[#7]

Hi,

I attached an example of the initial table (left) and of the result table (right) I want to obtain.

Hope now it is clear, but if you need more information do not hesitate to ask.

For what concerne the format question, in the reference manual I found in the definition of the function peek: 

-------------------------------------------------

peek( fieldname [, row [, tablename ]] )

Fieldname must be given as a string (e.g. a quoted literal)

------------------------------------------------

So my doubts,

thanks again.

[swuehl · Accepted solution]

I believe you can do it like this:

INPUT:

LOAD * INLINE [

DATAPREZZOLISTINOL     CATPRODT113    PREZZOLISTINOL

01.06.2012    3    1,03

02.06.2012    3    1,56

03.06.2012    3   

04.06.2012    3   

05.06.2012    3    1,24

06.06.2012    3   

07.06.2012    3    1,35

01.06.2012    5   

02.06.2012    5    1,02

03.06.2012    5   

04.06.2012    5    1,25

05.06.2012    5    1,65

06.06.2012    5   

07.06.2012    5   

01.06.2012    9    1,68

02.06.2012    9   

03.06.2012    9   

04.06.2012    9   

05.06.2012    9    1,25

06.06.2012    9   

07.06.2012    9    1,74

] (delimiter is '\t');

RESULT:

NOCONCATENATE LOAD

DATAPREZZOLISTINOL,

CATPRODT113,

if(len(trim(PREZZOLISTINOL))=0 and Peek('CATPRODT113') = CATPRODT113,peek('PREZZOLISTINOL'),PREZZOLISTINOL) as PREZZOLISTINOL

Resident INPUT order by CATPRODT113, DATAPREZZOLISTINOL;

drop table INPUT;

IINPUT table is just for loading your data inline instead from Excel or your DB. Note that I used a order by CATPRODT113, DATAPREZZOLISTINOL LOAD.

Since you need to take care of changes in CATPRODT113, I added an additional check for changes in that field.

I also used len(trim(FIELD))=0 instead of isnull() to also catch the blank field values (like in my inline load).

[#9]

Thanks! It works fine!