NullInterpret for Excel sources

[Vegar]

 I need help defining all 'NULL' values in Excel as NULL() in QlikView/Qlik Sense. I would prefer not to write an  IF(Field = 'NULL', NULL(), Field)  as Field  for all fields in my excels.

I'm trying to load data from a bunch of excel tables where Null-values are recorded with the cell value 'NULL'. I'm handling this by defining the NullInterpret as described in the the Qlik help section Creating NULL values from text files.

set NullInterpret='NULL';

This solution  works fine if my source is an INLINE but does not work  for Excel sources, please take a look at the picture below and the attached files.

Screenshot from my example qvw

 This question is valid for both QlikView and Qlik Sense. 

[Anil_Babu_Samineni]

Try this?

NullAsValue *;
Set NullValue = 'NULL';

[marcus_sommer]

Maybe you could use an approach like this one: filling-default-values-using-mapping

- Marcus

[tresesco · Accepted solution]

This could work on text files (as the topic header says). If you convert the excel to csv it would work.

[Vegar]

@Anil_Babu_Samineni and @marcus_sommer

Thanks, but your suggestion are not the solution to my problem, I do not want Null as Value I want 'NULL' to become NULL().

The Filling default values using mapping  was a great tip for use in the future, I was not aware that I could use successfully null() in an applymap.

@tresesco : You are correct. Converting the data to csv would give me my desired result, so if I don't find a better solution then I could do the following.

1. Read the excel
2. Store all tables to csv
3. Read the csv files while NullInterpret = 'NULL';

[marcus_sommer]

I think the reversed way to replace 'NULL' with null() should work, too - at least with this small example it does and I assume it would also work by an Excel as source:

NullMap: mapping load 'NULL', null() autogenerate 1;
map F2 using 'NullMap';

t0: load * inline [
F1, F2
a, 1
b, NULL
c, 2
];

- Marcus

[Vegar]

I guess you're right @marcus_sommer . That could work and I could apply it on all fields using the map...using command like this.

Map * using NullMapTable;

I'll give it an attempt tomorrow.

[Vegar · Accepted solution]

@marcus_sommer: Your solution worked. By applying a NULL to Null() Mapping for all field I get the desired result. 

I have one concern about speed. I have not done any testing, but  my experience is that extended use of applymap on large data set could slow the script significantly. In my case this is not an issue so I can implement this without trouble.

Hence SET NULLINTERPRET is an Qlik feature, I would prefer to use that feature if possible. So if anyone figure out how this is done for Excel, please post.

I'll close this case for now. 

[marcus_sommer]

In my experience are mappings very fast and nearly always the fastest way to add/transform data compared with any other measures. Even multiple and/or nested/concatenated mappings in loadings with millions of records work very well and fast.

Nevertheless this must create some kind of overhead compared to a load without it. I don't know how the replacing/adjustments with the NULL variables are technically implemented and if this feature also creates more or less overhead.

I never measured in this regard the run-times and I doubt that I would even try it because most there are other and more powerful measures to improve the load-performance, for example changing the source-type (xlsx are nearly the slowest type of loading because it are zipped xml-data) and/or implementing of incremental load-approaches.

Beside this I'm not sure that a general replacing of any "false" NULL with real null() in all fields and records is necessary and useful. Personally I adjust this and the reversed way rather seldom and only when there is a certain need (and the reversed way to fill NULL's with real values to make them selectable is more often).

- Marcus