topic Re: Value NULL in set analysis? in QlikView

Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 18:55:47 GMT

Hi,

How can I set value NULL in set analysis?

sum({<CostoVen={NULL}>} Sales) [convert from sum(if(IsNull(CostoVen),Sales)]

Tanks You

Re: Value NULL in set analysis?

Nicole-Smith — Fri, 17 Jan 2014 19:05:44 GMT

I'm guessing NULL would be the opposite of "is something":

sum({$-<CostoVen={'*'}>} Sales)

sum({$-<CostoVen={'?*'}>} Sales)

where $- means "not" and '*' or '?*' means "something"

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 19:32:45 GMT

Thanks. I'm also going to try sum({$-<CostoVen={"%*%"}>} Sales).

Re: Value NULL in set analysis?

jpapador — Fri, 17 Jan 2014 19:33:38 GMT

You could also make a null flag in your script.

If(Len(CostoVen) = 0, 1, 0) as NullFlag

Than your expression would look like this

sum({$<Nullflag={1}>} Sales)

Re: Value NULL in set analysis?

Nicole-Smith — Fri, 17 Jan 2014 19:34:55 GMT

I'm not sure what you're trying to do with the percent (%) signs. In QlikView the wildcards are asterisk (*) for zero or more characters and question mark (?) for one single character.

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 19:46:00 GMT

sum({$-<CostoVen={"%*%"}>} Sales) is OK.

Thanks.

Re: Value NULL in set analysis?

kiranmanoharrode — Fri, 17 Jan 2014 19:54:26 GMT

Hi Antonio

Try this

=sum({<CostoVen={""}>} Sales)

Regards

Kiran

Re: Value NULL in set analysis?

CELAMBARASAN — Fri, 17 Jan 2014 19:55:43 GMT

You could use the expression as below

Sum({<CostoVen-={"*"}>} Sales)

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 20:06:58 GMT

I could also make script :

NullAsValue CostoVen;

SET NullValue = 0;

but I cannot modify the script because of file.qvd very great that would not be adjourned.

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 20:09:18 GMT

Hi Kiran,

I have already tried. It doesn't work.

Thanks

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 20:28:31 GMT

Sorry, my sum({$-<CostoVen={"%*%"}>} Sales) it doesn't work,

but your suggestion doesn't even work.

Re: Value NULL in set analysis?

MayilVahanan — Fri, 17 Jan 2014 20:31:17 GMT

Try like this

Sum({<CostoVen-={"*"}>} Sales)

Hope it helps

Re: Value NULL in set analysis?

Nicole-Smith — Fri, 17 Jan 2014 20:32:32 GMT

Please post a sample .qvw

Preparing examples for Upload - Reduction and Data Scrambling

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 20:39:23 GMT

Hi Mayil,

I have already tried. It doesn't work.

Thanks

Re: Value NULL in set analysis?

Anonymous — Fri, 17 Jan 2014 20:40:07 GMT

I don't think that this is possible with set analysis. The only way would be to have a 1/0 flag defined in the load script as jpapador mentioned earlier.

The reason why this isn't possible with SA is that set analysis basically functions like static user selections. See my explanation in this thread: http://community.qlik.com/message/450877#450877.

Since QV doesn't allow selections of NULL values, it's not possible to create a set expression for NULL values.

Re: Re: Value NULL in set analysis?

Nicole-Smith — Fri, 17 Jan 2014 20:45:36 GMT

Actually Jeffrey Vermeire, it is possible to count null values using set analysis like the following:

=count({$-<Value={'?*'}>}Field)

Please see attached file for an example.

Re: Re: Value NULL in set analysis?

rwunderlich — Fri, 17 Jan 2014 20:46:06 GMT

I recommend reading the excellent paper "Null and Nothing" by Henric Cronström available at NULL handling in QlikView

In the section "NULLs and missing values in Set analysis", HIC shows three ways to select null in Set Analysis. Here's one way :

sum({1-<CostoVen={*}>} Sales)

-Rob

Re: Value NULL in set analysis?

Anonymous — Fri, 17 Jan 2014 20:49:32 GMT

Jeffrey,

I've used the solution that Celambarasan Adhimulam showed here many times, and it worked.

I don't disagree though that adding flags in script makes things simpler.

Regards,

Michael

Re: Value NULL in set analysis?

antoniotiman — Fri, 17 Jan 2014 20:52:02 GMT

Thanks Jeffrey,

Thanks to Everybody.

I have not found solution.

I will continue to use sum(if(IsNull(CostoVen),Sales)) that works well

or I modify the script inserting

NullAsValue CostoVen;

SET NullValue = 0;

and I reload the data.

Regards,

Antonio

Re: Value NULL in set analysis?

Anonymous — Fri, 17 Jan 2014 20:54:07 GMT

Nicole,

There is no need for "?" here, unless you want to count blank values as nulls.

Regards,

Michael

Edit: in the attached, there are both versions