topic NullAsValue in QlikView

NullAsValue

Fri, 11 Jul 2014 09:16:47 GMT

Good morning,

I wrote:

ODBC CONNECT TO abc;

NullAsValue *;

Set NullValue = '0 ';

SQL SELECT ......;

But null values remain, how can I do?

Thank you

Re: NullAsValue

its_anandrjs — Fri, 11 Jul 2014 09:25:03 GMT

Try with

ODBC CONNECT TO abc;

NullAsValue *;

Set NullValue = '0';

Set NullValue = '';

SQL SELECT ......;

Read the mention documemts

NULL handling in QlikView

Re: NullAsValue

its_anandrjs — Fri, 11 Jul 2014 09:29:02 GMT

Or you can use

NullAsValue *;

Set NullValue = '<NULL>';

ODBC CONNECT TO abc;

SQL SELECT ......;

Re: NullAsValue

alexandros17 — Fri, 11 Jul 2014 09:30:49 GMT

The syntax seems correct, are you sure null values come from the table and not from some join ?

Re: NullAsValue

Fri, 11 Jul 2014 09:32:52 GMT

Nothing..

All I want to do is setting all the null values at 0..

Re: NullAsValue

Fri, 11 Jul 2014 09:40:52 GMT

this is the expression that I have 'NULL'

=-Sum ({} <numero={'5'}> qty) * (pc)

I don't always 'NULL', most of the time it restituice a result.

Re: NullAsValue

Fri, 11 Jul 2014 09:45:07 GMT

The problem is in 'pc' because in the table sometimes is 'NULL'.

Re: NullAsValue

alexandros17 — Fri, 11 Jul 2014 09:46:19 GMT

Ciao Miriana,

are you italian?

When you set null with a value this means that all null values returned from DB became the value you set.

This is not valid for expression, and in your case, I think there are at least 2 errors:

The first is the syntax: Sum ({$<numero={'5'}}> qty) * (pc)

The second is related to pc that is a single value not aggregated in any way (Sum(pc)???)

Let me know

Re: NullAsValue

Anonymous — Fri, 11 Jul 2014 09:46:30 GMT

Hi,

You may check specific field instead,

NullAsValue qty, pc;

Set NullValue = '0';

Re: NullAsValue

Fri, 11 Jul 2014 09:51:02 GMT

The problem is in 'pc' because sometimes in the table is 'NULL'.

I've already tried to write:

NullAsValue pc;

Set NullValue = '0';

But nothing changed.

Re: NullAsValue

Anonymous — Fri, 11 Jul 2014 09:56:17 GMT

Hi,

Try adding NullInterpret function above null value function, you may have blank which are not converting into 0.

SET NullInterpret ='';

NullAsValue pc;

Set NullValue = '0';

Re: NullAsValue

alexandros17 — Fri, 11 Jul 2014 09:57:00 GMT

Where do you load the field PC, from a table or from an external file?

Re: NullAsValue

Fri, 11 Jul 2014 09:59:36 GMT

Nothing..

I tried to see the table and the values that are null QlikView tells me that in fact they are already at 0..

Re: NullAsValue

antoniotiman — Fri, 11 Jul 2014 10:09:44 GMT

Hi Miriana,

perhaps are You using MySQL DB ?

Antonio

Re: NullAsValue

Anonymous — Fri, 11 Jul 2014 10:10:40 GMT

Then in that case, you have problem in your expression. Can you add "Only" function for pc and check if it is working. Apparently it should be Sum(pc) but not sure about you dataset so cannot comment.

Sum ({$<numero={'5'}}> qty) * Only(pc)

Re: NullAsValue

Mon, 14 Jul 2014 06:43:32 GMT

No, I use SQL Server..

Re: NullAsValue

Mon, 14 Jul 2014 06:43:46 GMT

Nothing again..

Re: NullAsValue

Mon, 14 Jul 2014 06:44:01 GMT

From a table..

Re: NullAsValue

olivetwist — Fri, 22 Jan 2016 16:28:53 GMT

I think you would need to do a preceding load. it wont replace nulls in a select in my experience.