Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Null Values Handle


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.

8 Replies
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
Partner - Champion III
Partner - Champion III

try with:

Set NullValue = 'NULL' ;

Load A,B from x.csv;

let me know

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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