Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Why blanks are not read as NULL in Excel?

I have an Excel file (attached as mytest.xlsx) that has 5 columns out of which the last one can be blank. I would like to read this into QV and have those blanks being read as NULL.

Have followed this doc NULL – The Invisible Nothing‌  and tested the two approaches mentioned 1) Set NullInterpret = ''; and 2) If( Len(Trim(Field))>0, Field ) but none works, as I can select those '-' values in the table AND they are not shown in pivot table. Strange thing is that, in pivot table, when I un-checked "Suppress Zero-Values", they are all shown as zeros again confirming that blanks are not read as NULL.



Appreciate any helpful comments.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

I have used this..

LOAD date,

     unique_name,

     year,

     month,

     If(len(trim(total_vol))=0,Null(),total_vol ) as total_vol

FROM

[mytest.xlsx]

(ooxml, embedded labels, table is Sheet1) where(year>2000);

Now for total_vol, you will not get values having null, so no point of having selection.

If you want to select them..change

If(len(trim(total_vol))=0,'NULL',total_vol ) as total_vol


Let me know if I am misunderstanding your requirements.

View solution in original post

8 Replies
MK_QSL
MVP
MVP

try this..

If( len(trim(total_vol))>0, total_vol,Null()) as total_vol

Not applicable
Author

Nothing has changed.

MK_QSL
MVP
MVP

What is your expected output? Can you explain here?

rupamjyotidas
Specialist
Specialist

Maybe This

If( Len(Trim(Field))<1,'BLANK' ,Field ) as FIELD

Not applicable
Author

According to what I have read from the doc, NULLs are not selectable in QV. This is not the case in QV file attached, meaning that you can select '-' values in the left table. So clearly, blanks are not read correctly as NULLs. I want to read them correctly as NULLs.

m_woolf
Master II
Master II

I don't believe that you can select the null values in the total_vol field.

To test this, make a list box that displays total_vol. Then try to select the null values.

MK_QSL
MVP
MVP

I have used this..

LOAD date,

     unique_name,

     year,

     month,

     If(len(trim(total_vol))=0,Null(),total_vol ) as total_vol

FROM

[mytest.xlsx]

(ooxml, embedded labels, table is Sheet1) where(year>2000);

Now for total_vol, you will not get values having null, so no point of having selection.

If you want to select them..change

If(len(trim(total_vol))=0,'NULL',total_vol ) as total_vol


Let me know if I am misunderstanding your requirements.

evan_kurowski
Specialist
Specialist

Issac Shams wrote:

According to what I have read from the doc, NULLs are not selectable in QV. This is not the case in QV file attached, meaning that you can select '-' values in the left table. So clearly, blanks are not read correctly as NULLs. I want to read them correctly as NULLs.

Good point Isaac.


What has confused the issue in the past is that the empty string '' (single quotes with nothing in between, aka ticks) IS selectable, while true Null chr(00) is not.


A listbox will show empty quotes '' as an entry and this can be used as a cross-table association.

True Null chr(00) will not produce a listbox entry, nor can be used as a field association.

In the past I've seen applications that created cross-table field associations on matching empty strings ''.  The value is completely invisible, and will test as a Len() = 0.  This is often where miscommunication will come into play as people will then describe this field as "Null" which isn't quite technically true in the Qlik construct, but you can see where it is also kind of applicable.