topic Re: replace null values in QlikView

replace null values

Tue, 10 Jul 2012 11:24:31 GMT

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!

Re: replace null values

swuehl — Tue, 10 Jul 2012 11:43:14 GMT

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;

Re: replace null values

Tue, 10 Jul 2012 11:47:18 GMT

This should do it from the script.

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

Saludos

Paco

Re: replace null values

giakoum — Tue, 10 Jul 2012 11:47:46 GMT

Please see attached.

[Final]:

LOAD

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.

Re: replace null values

Tue, 10 Jul 2012 12:54:02 GMT

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!

Re: replace null values

swuehl — Tue, 10 Jul 2012 12:59:55 GMT

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?

Re: replace null values

Tue, 10 Jul 2012 14:52:32 GMT

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.

Re: replace null values

swuehl — Tue, 10 Jul 2012 15:03:10 GMT

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

Re: replace null values

Tue, 10 Jul 2012 15:52:38 GMT

Thanks! It works fine!