topic Re: NullInterpret for Excel sources in QlikView

NullInterpret for Excel sources

Vegar — Sat, 16 Nov 2024 21:48:39 GMT

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.

Re: NullInterpret for Excel sources

Anil_Babu_Samineni — Thu, 29 Nov 2018 09:57:59 GMT

Try this?

NullAsValue *;
Set NullValue = 'NULL';

Re: NullInterpret for Excel sources

marcus_sommer — Thu, 29 Nov 2018 10:01:01 GMT

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

- Marcus

Re: NullInterpret for Excel sources

tresB — Thu, 29 Nov 2018 10:22:43 GMT

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

Re: NullInterpret for Excel sources

Vegar — Thu, 29 Nov 2018 12:07:50 GMT

@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.

@tresB : 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.

Read the excel
Store all tables to csv
Read the csv files while NullInterpret = 'NULL';

Re: NullInterpret for Excel sources

marcus_sommer — Thu, 29 Nov 2018 13:14:35 GMT

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

Re: NullInterpret for Excel sources

Vegar — Thu, 29 Nov 2018 15:52:15 GMT

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.

Re: NullInterpret for Excel sources

Vegar — Thu, 29 Nov 2018 21:48:51 GMT

@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.

Re: NullInterpret for Excel sources

marcus_sommer — Fri, 30 Nov 2018 07:50:43 GMT

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