Removing Rows with Nulls

[#1]

Good afternoon, ive got a situation where I am getting nulls in some of my fields, I know why they are there and it doesnt matter. My Flat table data is a mix of Dimension data and Expression data. How do I go about excluding an entire row from my flat table if a field has a Null value in it please?

Very Kind regards.

Paul.

[hic]

Load ... from ... where not IsNull(field) ;

or

Load ... from ... where Len(Trim(field))>0 ;

[#3]

Hi Henric, thanks for the response, the particular field that has the null value is not calculated in the script its an expression that loads all relative data including nulls. I want qlikview to not load rows where any row contains a null either in dimension or expression.

[hic]

Not sure I understand...

If you do not want QlikVIew to load these records, then you should use a where clause in the script where the condition tests for NULL.

If you, on the other hand, want QlikView to load all records but just use some of the records for a specific calculation, then you can use a similar condition inside the calculation i.e. inside the aggregation function, e.g.

   Sum( if(not IsNull(field), Amount) )

   Sum( {$<field={"*"}>} Amount )

HIC

[#5]

Hi Heric, Here is the script for the table I am using :-

Data_table:

LOAD Internal_ID_For_Meter_Reading_Document,

     Meter_Reading_Reason

FROM

**

where Meter_Reading_Reason='01'

or Meter_Reading_Reason='02'

;

I am using an expression which shows data in the same row which conforms to either one of the 'Meter reading' conditions above, but at the same time its pulling data that doesnt fit '01 or '02' hence the null.

I want the script to not load the row at all if either '01' or '02' are not fullfilled. Trouble is if I use the And instead of 'OR' No data will load at all.

[hic]

Your Load statement looks OK. It should only load records where Meter_Reading_Reason is either '01' or '02'. But you say that it loads records where Meter_Reading_Reason is NULL?

HIC

PS.

Use "... where Match(Meter_Reading_Reason,'01','02')" instead. Then it is esier to add or remove individual values.

[#7]

I like the use of 'Match' very efficient way of doing it, however its still loading a row if an expression contains a value that relates to a meter reason thats not '01' or '02' but when I try and load the expression and tell it to oly load what I want for example :-

FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3) and Meter_Reading_Reason='01' or Meter_Reading_Reason='02' it makes all values in that table a zero. Am at a loss as to why this is happening!

[hic]

Can you post the file with a small part of the data? So we can find the problem?

HIC

[vincent_ardiet]

Hi,

Have you another table linked to this one in your datamodel ?

Regards,

Vincent

[#10]

To suppress dimensions where all expressions result in NULL, go to the "Presentation" tab and check the box for "Suppress Missing".

To suppress expressions where the dimension is NULL, go to the "Dimensions" and, for that dimension, and check the box for "Suppress When Value Is Null".

To hide a dimension based on a more advanced criteria, such as a specific expression returning NULL (ignoring the results of the other expressions), create a new calculated dimension with that expression.  For that dimension, check the box for "Suppress When Value Is Null".  Finally, go to "Presentation", and, for that column, select the option for "Hide Column", which will prevent it from showing.  That allows you to have advanced control over the display of dimensions by hiding any rows where the dimension returns NULL, which can occur for any reason you want.