topic Data cleansing in qlikview in Qlik Learning Discussions

Data cleansing in qlikview

Tue, 30 Sep 2014 13:29:42 GMT

Can we clean the data set i.e. excel for null observations before using it for visualization?

I have a worksheet survey where there are some observations with null value. I want to remove those observations in qlikview. Can you please suggest me some way to do this?

Re: Data cleansing in qlikview

bme — Tue, 30 Sep 2014 15:15:27 GMT

There's a pretty good write up here.

NULL handling in QlikView

As a quick pointer I usually use "if(Len(Trim(Field))>0,Field,NullSubstitute)" for text fields or "Alt(Field,NullSubstitute)" for numeric fields. The NullAsValue functionality will help for more widespread null issues.

Re: Data cleansing in qlikview

Wed, 01 Oct 2014 05:10:37 GMT

Hi Ben,

Thanks for the reply. I am currently using set analysis to handle this. But my question is at the time of data load, can we eliminate some rows if there is no data available?

Pinaki

Re: Data cleansing in qlikview

bme — Wed, 01 Oct 2014 14:17:09 GMT

Sure just use a where clause:

Load

     Field1,

     Field2,

     Field3

From SomeFile.qvd (qvd)

Where Len(Trim(Field1))>0

You could also use the Isnull() function, but I prefer the Len(Trim( method as it also eliminates zero length strings which would pass the Isnull() check.