topic Re: Removing Rows with Nulls in QlikView

Removing Rows with Nulls

Thu, 31 May 2012 12:25:12 GMT

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.

Removing Rows with Nulls

hic — Thu, 31 May 2012 12:32:57 GMT

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

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

Removing Rows with Nulls

Thu, 31 May 2012 12:38:30 GMT

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.

Removing Rows with Nulls

hic — Thu, 31 May 2012 12:50:49 GMT

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

Removing Rows with Nulls

Thu, 31 May 2012 13:03:57 GMT

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.

Removing Rows with Nulls

hic — Thu, 31 May 2012 13:18:48 GMT

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.

Removing Rows with Nulls

Thu, 31 May 2012 14:24:07 GMT

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!

Removing Rows with Nulls

hic — Thu, 31 May 2012 14:28:32 GMT

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

HIC

Removing Rows with Nulls

vincent_ardiet — Thu, 31 May 2012 14:35:07 GMT

Hi,

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

Regards,

Vincent

Removing Rows with Nulls

Mon, 11 Jun 2012 18:02:41 GMT

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.

Re: Removing Rows with Nulls

Tue, 12 Jun 2012 10:38:44 GMT

Thanks for the response, I am trying to Not load a row if any of the expression fields are Null, all Dimension fields in my script will contain 0 Nulls.

Re: Removing Rows with Nulls

Tue, 12 Jun 2012 13:52:01 GMT

When you say "script", it makes me think of when you are initially loading the data into QlikView. The advice I gave is used in the chart design stage.

If I understand what you are saying, and you are designing a chart, then put all of those expressions into the hidden column I mentioned in the third part of my previous post. I think something like the following would be helpful:

If(

IsNull(Expr1) = 0 AND IsNull(Expr2) = 0 AND IsNull(Expr3) = 0 ...etc.,

True(),

Null()

)

Re: Removing Rows with Nulls

hirenp0904 — Mon, 05 Nov 2012 17:31:13 GMT

I am having the same problem with my straight table and i tried to supress missing but no change. I am trying the hide dimensions where my expressions are '-'

Re: Removing Rows with Nulls

hic — Mon, 05 Nov 2012 18:01:16 GMT

The suppress missing on the Presentation makes the chart suppress values where the expression has a missing value. But you want to do it where the dimension has a missing (Null) value. You should set this on Chart properties -> Dimensions -> Suppress when value is Null.

HIC

Re: Removing Rows with Nulls

hirenp0904 — Mon, 05 Nov 2012 18:37:36 GMT

Henric,

Actually i want to supress row where my expression has missing / null / blank value. My dimensions do not have any null values. However i tried with both the options but no luck.

Re: Removing Rows with Nulls

swarnav999 — Mon, 28 Jan 2019 23:03:51 GMT

Works! Thank you.