topic Re: Exclude nulls from script in QlikView

Exclude nulls from script

Thu, 03 Apr 2014 16:35:47 GMT

I need to exclude nulls from script.

Re: Exclude nulls from script

fernando_tonial — Thu, 03 Apr 2014 16:41:36 GMT

Hi, Try the function IsNull(Field)

LOAD * from Table Where Not IsNull(Field);

Best Regards.

Tonial.

Re: Exclude nulls from script

regowins — Thu, 03 Apr 2014 16:41:37 GMT

At the end of you script you can do somthing like

Where Len(fieldname) > 1

Re: Exclude nulls from script

Thu, 03 Apr 2014 16:48:18 GMT

Hi,

To load without nulls I usually put:

Where not(IsNull(FieldName))

Also works, for nulls and empty strings:

Where Len(FieldName)>0

To also exclude empty spaces:

Where Len(Trim(FieldName))>0

A remark: when you load from qvd or Excel, you have real null values; when you load from csv or txt, you get empty strings instead, but 2nd and 3rd formula work.

Hope this helps.

Re: Exclude nulls from script

Thu, 03 Apr 2014 17:32:49 GMT

Doesn’t work for my need.

Re: Exclude nulls from script

Thu, 03 Apr 2014 17:32:49 GMT

Doesn’t work for my need.

Re: Exclude nulls from script

Thu, 03 Apr 2014 17:33:25 GMT

Doesn’t work for my need.

Re: Exclude nulls from script

PradeepReddy — Thu, 03 Apr 2014 18:01:46 GMT

Hi Paul,

What is the exact requirement?

Re: Exclude nulls from script

Thu, 03 Apr 2014 19:20:50 GMT

In the example below, I do not want the PSalesNumber (example numbers below) 2705, 2702,2699, etc. to appear.

Re: Exclude nulls from script

regowins — Thu, 03 Apr 2014 19:29:26 GMT

What does your load script for this data look like ?

Re: Exclude nulls from script

crusader_ — Thu, 03 Apr 2014 19:31:23 GMT

Hi Paul,

As I can understood PSalesNumber is a Key Field,

and it contains in other tables with more data.

Try to Choose the field with NULL and check "Omit rows where field is null" in your Tablebox Properties on Presentation Tab.

Hope it helps.

Andrei

Re: Exclude nulls from script

Thu, 03 Apr 2014 19:34:02 GMT

SYSTEM_COMMISSPAYSALES:

//LEFT Keep (SP_INFO)

LOAD distinct

SalesDescription,

SalesNumber as sales_number,

PSalesName,

ApplyMap('MAP_PSALESNUMBER', PSalesNumber) As PSalesNumber,

////PSalesBaseRate,

PSalesNumRate

////StartDate,

FROM

$(vQVDPath)\Data\Sales\SYSTEM_COMMISSPAYSALES.qvd (qvd);

//where SalesNumber > 0;

//where Not IsNull(PSalesName);

//where PSalesNumRate<>999;

//where Len(Trim(PSalesName))>0;

//where not(IsNull(PSalesName));

Re: Exclude nulls from script

fernando_tonial — Thu, 03 Apr 2014 19:35:46 GMT

Maybe you try in GUI.

Check the box Suppress When Value is Null in page Dimesion for Chart Objetc or Omit Rows Where Field is NULL in page Presentation for Table Box Objetc.

Chart Object.

Table Box

Best Regards.

Tonial.

Re: Exclude nulls from script

Thu, 03 Apr 2014 19:39:26 GMT

This removed those null records. Do you know when they appeared ?

Thank you.

Re: Exclude nulls from script

crusader_ — Thu, 03 Apr 2014 19:47:03 GMT

As I wrote it is because of using key field in Table box , better to use a Straight table Chart in that case.

Table box include every possible value of your field in your schema. (Associative model)

For example you have 2 tables:

KeyF Sum

1 100

2 130

and

KeyF Qty

1 10

2 30

3 50

TableBox returns:

KeyF Sum

1 100

2 130

3 - e.g. NULL

Just keep it in mind.

Hope it helps.

Andrei

Re: Exclude nulls from script

Thu, 03 Apr 2014 19:52:38 GMT

Yes. Thank you.