topic Loading blank from Excel in QlikView

Loading blank from Excel

mpressey — Wed, 25 Nov 2020 16:16:04 GMT

I have an Excel workbook from which Qlikview is loading data. The cells are blank until data is input. It is possible for the cells to contain 0's after data is input. I'm trying to create a text box with a conditional message and color to alert end users whether or not data had been input. However Qlikview is treating the blanks as 0's so the conditional set analysis does not work. Is there a way to modify the load script so that Qlikview can distinguish between blanks and 0's?

Re: Loading blank from Excel

Gysbert_Wassenaar — Mon, 31 Oct 2016 17:15:40 GMT

If Qlikview load 0 values then the excel file doesn't contain blanks, but 0 values. In the excel file only the formatting has been changed to show 0 values as blanks. The values themselves are still 0 values.

In Qlikview replace 0 values will nulls or blanks in the script with something like this:

LOAD

...some fields...

if(FieldA = 0, Null()) as FieldA,

if(FieldB = 0, '')) as FieldB,

...some other fields....

FROM

MyExcel.xlsx (ooxml, embedded labels, table is Sheet1)

;

Re: Loading blank from Excel

engishfaque — Mon, 31 Oct 2016 18:23:09 GMT

Hi Mike,

Use listed below script,

Load

Field(s),

if(IsNum(Field), Field, '') as newField,

From yourDataSource.xls;

Kind regards,

Ishfaque Ahmed