NullAsValue not working...Why?

[vadim_grab]

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) ;

[pooja_prabhu_n]

Do not specify any values in NullInterpret, Try this

NULLASVALUE *;

SET NullValue ='Null';

SET NULLDISPLAY='Null';

SET NullInterpret ='';

[vadim_grab]

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?

[vadim_grab]

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?

[vadim_grab]

NULLASVALUE *;

SET NullValue ='Null';

SET NULLDISPLAY='Null';

SET NullInterpret ='';

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

[evan_kurowski]

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)

[vadim_grab]

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?

[evan_kurowski]

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.