topic Re: Suppressing NULL values in a sum in QlikView

Suppressing NULL values in a sum

Wed, 30 May 2012 13:40:55 GMT

I am processing a sales file which includes the "SaleAmount" and the "LeadSource". In many cases, the LeadSource field is either blank or NULL. When I load the data file into Excel to inspect it, some of these "missing" LeadSource files come up as "-" (i.e. a hyphen, ASCII char 45), or they come up as completely null - i.e if you test them in Excel with IsBlank, it returns FALSE (the cell in NOT blank), and using a cell viewer utility, there is no character whatsovever in the field (that is, the cell is what I would call "null"). I've read a bunch of topics here, and I've tried various script combinations of NullAsValue LeadSource, Set NullValue as "TestNull", Set NullDisplay as "TestNull", but when I reload, there is still a completely blank entry in the LeadSource table AND an entry "-" in the LeadSource table, both of which have a number of values associated with them that skew the results we're interested in. So, I have two questions: 1) How do I get these null values in my data to show up as "TestNull" in my table (I want to see this just to ensure I know what's going on), and more important, how, when I calculate the sum of "SaleAmount" do I get the formula to ignore the rows that have 'null' in LeadSource? If I were writing procedural code, it would be: IF LeadSource = null THEN TotalSale = (TotalSale+ 0) ELSE TotalSale = TotalSale + SaleAmount. But I'm stumped after a number of tries of doing this with sum(if.. in various configurations. Thanks in advance for your help.

Suppressing NULL values in a sum

Wed, 30 May 2012 14:11:09 GMT

Hi,

have you tried in your script to force the field in this way:

IF(len(Trim(LeadSource))>0,LeadSource,Null()) as LeadSource

Hope it helps

Giampiero

Suppressing NULL values in a sum

swuehl — Wed, 30 May 2012 14:18:22 GMT

Not sure if I fully got your setting, you want to do all the checks / calculations in the script?

You could check for blank or NULL field values (see also

http://community.qlik.com/message/182388)

using in your load something like

if(Len(trim(purgechar(Field, chr(160))))=0,'Test',Field) as Field,

or maybe try isnum() function

if(not isnum(Field),'Test',Field) as Field,

To calculate the sum, you could use rangesum() which will treat all non-numeric values as zero:

rangesum(peek(TotalSale), SaleAmount) as TotalSale,

Hope this helps,

Stefan

Re: Suppressing NULL values in a sum

Wed, 30 May 2012 14:53:00 GMT

Thanks for your help; let me try to be more clear:

In the script, I'm trying to change the value of the LeadSource field so that the null values show up as something I can read (e.g. "TestNull"). As I said, this is just so that I know what's going on with the data, (and maybe to show sales people why it's helpful to fill in all the fields..)

But to calculate the total sales, I am trying to use an expression within the chart. A simple sum(SaleAmount) doesn't work, as there are thousands of records that have a SaleAmount but which also have a null value for LeadSource. I don't want to make this calculation in the script.

Hope that is more clear.

Re: Suppressing NULL values in a sum

swuehl — Wed, 30 May 2012 15:01:46 GMT

So in a chart you probably want something like

=sum( if( not Len(trim(purgechar(LeadSource, chr(160)))), SaleAmount))

Or you work out a field value replacement like suggested by giampero and just check for not isnull(LeadSource).

Re: Suppressing NULL values in a sum

Wed, 30 May 2012 15:57:57 GMT

Hi, I tried copying and pasting your formula directly into my script, but it highlights the comma after the ">0", and gives me "error in expression" when I try to load the script. Any thoughts?

Re: Suppressing NULL values in a sum

swuehl — Wed, 30 May 2012 16:33:35 GMT

Hm,

I don't get an error highlighting with one of my script lines or giampero's line. My last post's line is for use in a chart expression.

No idea why you get an error in your script, could you post (the relevant part of) your script here? Or a sample QV file?