topic Re: Null Values Handle in QlikView

Null Values Handle

Tue, 27 Jan 2015 07:48:17 GMT

Hi All,

i have a CSV file, in that file i have some NULL fields and as well as some NULL value i want to display a string '*NULL*'

i don't know which field having NULL & where are the NULL Values .

While i am loading the CSV file in QV, At that time i need to load String(NULL) in to QV.

How Can i do this Please Help me.

Re: Null Values Handle

its_anandrjs — Tue, 27 Jan 2015 07:54:25 GMT

Hi,

You can try this way

T1:

Load

ColA, ColB;

LOAD * INLINE [

ColA, ColB

A,1588

C,1251

];

T2:

LOAD

ColA, ColB,

if(Len(ColB)=0, '*NULL*', ColB) as [New Column]

Resident T1;

Regards

Anand

Re: Null Values Handle

alexandros17 — Tue, 27 Jan 2015 07:55:35 GMT

try with:

Set NullValue = 'NULL' ;

Load A,B from x.csv;

let me know

Re: Null Values Handle

Wed, 28 Jan 2015 05:11:39 GMT

Hi Thank you for you help,

in my file, If i don't know which field having null and which row having null at that time.

How can i replace that nullvalue with NULL string.

Re: Null Values Handle

alexandros17 — Wed, 28 Jan 2015 07:49:17 GMT

It's automatic, each time there is a Null the system replace it with the value (Text) "Null"

Re: Null Values Handle

Thu, 29 Jan 2015 09:49:57 GMT

Hi,

I tried

Set NullValue = 'NULL' ;

Load A,B from x.csv;

But it is not working.

i added my qvw file please check

Re: Null Values Handle

alexandros17 — Thu, 29 Jan 2015 10:08:55 GMT

Unfortunately the load inline uses blanks and not null, take a look to the document

Re: Null Values Handle

Anonymous — Thu, 29 Jan 2015 12:11:12 GMT

Have you tried Anand's solution ? It should work perfectly.

If you are not sure about the columns which are having null

. Just use expression for all the columns.

if(Len(ColB)=0, '*NULL*', ColB) as [New Column]

if(Len(ColC)=0, '*NULL*', ColC) as [New Column2]

if(Len(ColD)=0, '*NULL*', ColD) as [New Column3]

....

.if(Len(ColN)=0, '*NULL*', ColN) as [New ColumnN]

Resident T1;

Re: Null Values Handle

Gabriel — Thu, 29 Jan 2015 12:29:46 GMT

Hi,

I will have a Mapping table for NULL

Map_Nulls:
MAPPING LOAD
NULL(),
'<NULL>' // This could be UNKNOWN or whatever
Autogenerate 1;

Then before your main load script

Have

Map

* // Or specify fields you'll like to apply the null values to

Using Map_Nulls ;

This way you will catch all the NULL values in the data.

Hope this helps