topic Re: NullAsValue not working...Why? in QlikView

NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 09:34:59 GMT

Hello, All

I have tried all known to me solutions to reflect 'Null' for all Null value.

As result worked only this: If(Len(Trim(city))=0,'Null',city) as city1

As you understand it's not the most elegant way. Maybe someone has penetrated this problem more deeply?

WHY?

I will be grateful for any comments and ideas!

Script code:

SET NullInterprete= 'Null';

SET NullDisplay= 'Null';

SET NullValue='Null';

NullAsValue city;

City:

LOAD

city,

If(Len(Trim(city))=0,'Null',city) as city1,

FROM

(txt, utf8, embedded labels, delimiter is ',', msq) ;

Re: NullAsValue not working...Why?

tresB — Fri, 20 Apr 2018 09:55:08 GMT

A quick try; spelling correction, remove extra 'e' may be.

SET NullInterpret= 'Null';

Re: NullAsValue not working...Why?

Chanty4u — Fri, 20 Apr 2018 10:02:12 GMT

try this also

NULLASVALUE *;

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 10:06:22 GMT

Thanks! It did not help

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 10:07:00 GMT

did not help! Thanks

Re: NullAsValue not working...Why?

tresB — Fri, 20 Apr 2018 10:10:24 GMT

What are you exactly trying to achieve? 'Null' to be treated as real null() that should not appear in the listbox? I guess few of your null declarations are contradictory.

Re: NullAsValue not working...Why?

techvarun — Fri, 20 Apr 2018 10:14:33 GMT

NullasValue:
Mapping Load
Null(), 'Null'  AutoGenerate 1;


MAP City USING NullasValue;

You may try Map using statement

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 11:08:31 GMT

Pls, look at screenshot. City = City1 = 'Null'

Re: NullAsValue not working...Why?

tresB — Fri, 20 Apr 2018 11:33:56 GMT

Sorry, I see that too. But I don't see what is not there, i.e. - that you want it to be like.

Re: NullAsValue not working...Why?

marcus_sommer — Fri, 20 Apr 2018 11:47:42 GMT

Your value isn't NULL else it contained any not visible/printable chars or is just empty which means something like: = '' and which is not NULL and therefore all the NULL features from Qlik won't work. In your case it's quite obvious within in your listbox because you could select this value - NULL instead wouldn't be displayed there and is also in other objects like tables not selectable.

Therefore your solution is a good one and very common. An alternatively to this might be an mapping-approach like this one: Filling Default Values Using Mapping | Qlikview Cookbook.

- Marcus

Re: NullAsValue not working...Why?

pooja_prabhu_n — Fri, 20 Apr 2018 12:17:52 GMT

Do not specify any values in NullInterpret, Try this

NULLASVALUE *;

SET NullValue ='Null';

SET NULLDISPLAY='Null';

SET NullInterpret ='';

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 13:26:18 GMT

I don't understand the logic. Can you explain? Many thanks

What I understand: part code with creating MissingMap table with key - Null() and value - 'Null'

Where are I have to using MAP in my case? before or after BusinessTable?

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 13:35:27 GMT

Markus, thanks for detailed explanation.

First of all, I really wanted to understand why? now the question is "why?" is closed.

Second - solution of the problem by the way you (and @Varun Prakash Paulraj) propose. Can you explain that part?

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 14:19:45 GMT

NULLASVALUE *;

SET NullValue ='Null';

SET NULLDISPLAY='Null';

SET NullInterpret ='';

Thanks! It worked for city field, but didn't work with other 'Null' value

Re: NullAsValue not working...Why?

evan_kurowski — Fri, 20 Apr 2018 14:39:54 GMT

Just want to lend support to Vadim, I have been there with you on this topic. Null interpret, and giving the empty spaces of data tangible handles to grasp & manipulate, has been one of the most important features of the entire Qlik experience.

When it works, it is a beautiful & convenient thing, but sometimes things beyond syntax had ways of "detaching" this functionality. You can search for script workarounds & make code adjustments, and maybe eventually get it working again, but.. that doesn't remove the uneasy feeling symptoms could return, and root cause is still lurking.

The Qlik manual does define different quoting rules for a .csv text file vs. Excel having different syntax to detect Null. Whitespace encapsulated in Excel will sidestep null trapping unless Trim() applied (and I've seen apps that actually use various combinations of spaces & tabs as an intentional form of invisible morse-code), while .csv will treat unquoted whitespace as null. However I have witnessed these rules break down on more than one occasion on apps that have not been script modified, with the prior load detecting properly and subsequent loads missing detection. (will begin wearing a body-cam on programming excursions to prove it from now on ) (ok not really gonna wear cams, but.. this really does happen)

NullInterpret

The defined symbol will when it occurs in a text file, Excel file or an inline statement be interpreted as NULL. A user-defined variable.

Example:

set NullInterpret=' ';

set NullInterpret =;

will NOT return null values for blank values in Excel (but it will for a csv text file)

set NullInterpret ='';

will return null values for blank values in Excel (but will NOT for a csv text files)

Re: NullAsValue not working...Why?

vadim_grab — Fri, 20 Apr 2018 15:34:05 GMT

Evan,

Thanks for your expirience and very deep understanding my problem!

"that doesn't remove the uneasy feeling symptoms could return, and root cause is still lurking."

Lack of a sense of 100% control of the situation is the worst thing that can be for me!

In my case I solved the problem, but "uneasy feeling" does not leave my mind!

What will help next case?

Re: NullAsValue not working...Why?

evan_kurowski — Sun, 22 Apr 2018 05:21:00 GMT

Absolutely Vadim, agree with you entirely. You can put that quote in bold.

But i don't want you to get the impression I am down on Qlik, I will vouch for it as the premiere BI software. Whatever issues it faces or honestly owns up to, doesn't mean the competition gets to gloat, as they likely have less immunity or insight to the very same.

I've dedicated the last 8 years of my professional life as a Qlik specialist. My career is bound to its fate, and without it I would need a major reinvention, so believe me I want to protect it.

But something has changed however, in the past few years.. in the ways that corporate assignments are fulfilled, and the way that corporate environments behave.

While I've been studying Qlik for 8, I have been programming since the early 80's, with an Apple IIe, and high school classes in Fortran & Pascal. So again with that in mind, I'm going to assert that something has recently changed. (I'm sure we can find some with longer tenure or more senior positioning who will attest "Bah! Nothing is happening!", which presents the reader with an interesting dilemma: to believe them or me. Fielder's choice.)

There's a stronger "corrosive radiation" that seems to pick out key pieces of DNA from all programs, so that when you leave a routine or application in a working state, there's a high likelihood when you return to the same after hiatus, the chances it is functional when you return are slim. Maybe just a few minor breaks, but enough to require a fresh round of maintenance.

There's something changing about the programmatic environments themselves, and not sure if these vectors are being introduced from anti-Virus, enterprise process monitoring, or network administration policy, but the attributes of your programmatic platform can change while you work.

We talked about the "in-motion" properties of null detection. I have one particular client who has broken the NullAsValue on one of our apps at least 3 times in the past year. It doesn't matter if the app has been handling nulls fine for months, suddenly it stops working, while the inputs look just like ordinary spreadsheets.

Another good example of this is file encoding. Almost every text file I receive as data or create via process will start out with defaults of UTF8, until you can almost take for granted the encoding. Once complacent with encoding, then the process may suddenly switch to some sort of endianness and even pick up byte order marks (has someone started routing your inputs through unix boxes?).

When this comes from data suppliers at first I'm like "haha I get it, you tricked me" and then assume it will stop. But it doesn't stop, and the pattern repeats every single time. Then it starts to dawn, this isn't mere coy probing, this is a checklist to grind out the process, and slow production.

(after each step the developer should be encouraged to complete a coding pass)
Deliver the intial data files
Change some fields / Change the table structure
Change the encoding
Change the metrics
Combine incongruent aggregation levels
Request features tied to known bugs or server incongruences
Delay the data / Slow-track the project
(wash, rinse, repeat)

So the issue is, while Qlik software gives you some pretty amazing data capabilities, we still suffer from that Jedi to stormtrooper ratio. A light sabre might fend off 5 blasters,10 blasters.. but after that eventually things get overwhelmed. There's not a huge population of Qlik developers in circulation, and if you're organization is isolated to maybe 2-3, while retaining say like 500 JOOMLA programmers, you had better believe you will be on the defensive from the JOOMLA MAFIA during the course of your work. You likely have two options, stay under the radar, or accede and let potential market rivals rationalize your data sources and output.

I don't ascribe these shortcomings to Qlik, but instead believe there's a deeper environmental corruption at play. We need to be honest with ourselves in what works in the perfect conditions of lab, vs. what offers terrain cover out in the field. Otherwise we run the risk of having two different realities, and with such stake in the matter should reject the seduction of hubris against false-complacency.

The competition is envious, and have a Solieri attitude. Since they can't fault the technique, they are attacking the process, and using advantage of numbers, and we have to adapt for that. Should defensive programming become a mandatory curriculum for all programmers seeking to retain some form of mindful independence, and not be absorbed by the most influential faction to occupy each office? Tight code examples and brief time-frames of development are touted for all to see, but methodically something moves the tracks off the straightest line, and again, till the eventual result is complexity, duration, & maintenance.