Null Values Handle

[#1]

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.

[its_anandrjs]

Hi,

You can try this way

T1:

Load

ColA, ColB;

LOAD * INLINE [

    ColA, ColB

    A,1588

    B,

    C,1251

];

T2:

LOAD

ColA, ColB,

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

Resident T1;

Regards

Anand

[alexandros17]

try with:

Set NullValue = 'NULL' ;

Load A,B from x.csv;

let me know

[#4]

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.

[alexandros17]

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

[#6]

Hi,

I tried

Set NullValue = 'NULL' ;

Load A,B from x.csv;

But it is not working.

i added my qvw file please check

[alexandros17]

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

[Anonymous]

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;

[Gabriel]

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