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: 
mpressey
Creator
Creator

Loading blank from Excel

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?

2 Replies
Gysbert_Wassenaar

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)

     ;


talk is cheap, supply exceeds demand
engishfaque
Specialist III
Specialist III

Hi Mike,

Use listed below script,

Load

      Field(s),

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

From yourDataSource.xls;

Kind regards,

Ishfaque Ahmed