Why blanks are not read as NULL in Excel?

[#1]

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.

[MK_QSL]

try this..

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

[#3]

Nothing has changed.

[MK_QSL]

What is your expected output? Can you explain here?

[rupamjyotidas]

Maybe This

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

[#6]

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]

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 · Accepted solution]

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]

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.