Difficulty with NULLASVALUE

[#1]

Hi all,

I'm new to Qlik. I've generally been able to answer questions via previous discussions (thanks to everyone who contributes). Unfortunately, I have not been able to figure out an issue I'm having with NULLASVALUE using previously answered questions in the community forums.

Need: my need is to be able to select null values in a sheet table which I will then export. I'm pulling from several QVD loaded tables.

Problem: I've included 'set NULLASVALUE='<NULL>';' in each applicable sheet of a load script and also tried placing the statement in a sheet of other set statements (although I did these at different times) at the beginning of the load. The example code below includes a "where 1=1" statement that was recommended in another community post because of issues with optimized QVDs. I've tried loading with and without this statement.

I'm having issues with the '<NULL>' sometimes being written and available, other times getting blanks, and other times getting a "-" for the null value as is default.

Any suggestions would be most welcome.

****************************

GIFTUDFtemp:

NullAsValue*;

LOAD

  "Gift ID",

   [Gift Alt Group Code],

   [Gift Mission Flag]

FROM [QVD]

(qvd)

where 1=1;

****************************

I've replaced the QVD just to ensure I'm compliant with organizational privacy policies.

Thank you,

Brian

[sunny_talwar]

Have you specified what the value needs to be?

NullAsValue *;

Set NullValue = 'NULL';

GIFTUDFtemp:

LOAD

  "Gift ID",

  [Gift Alt Group Code],

  [Gift Mission Flag]

FROM [QVD]

(qvd)

where 1=1;

[#3]

Hi Sunny,

Yes. Sorry I had a bad copy/paste. I have 'set NULLASVALUE='<NULL>';' in a group of set statements in the first load sheet I have. I've also tried placing it this in each load sheet as follows:

GIFTUDFtemp:

set NULLASVALUE='<NULL>';

NullAsValue*;

LOAD

  "Gift ID",

   [Gift Alt Group Code],

   [Gift Mission Flag]

FROM [lib://QVD - Donor Perfect/DonorPerfect.GiftUDF.QVD]

(qvd)

where 1=1;

Thanks,

Brian

[sunny_talwar]

Difficult to say unless we can see it or test it out

[antoniotiman · Accepted solution]

Hi Brian,

as Sunny wrote

set NULLASVALUE='<NULL>';

SET NullValue = '<Null>';

However try like

If(Len(Trim([Gift ID])) = 0,'<Null>',[Gift ID]) as [Gift ID],

etc.

Regards,

Antonio

[dwforest]

According to Help...

http://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/NullAs...

You need 2 lines, assuming you are replacing null GiftID's

NullAsValue GiftID;

Set NullValue='<Null>';

Personally, I go about it by setting a flag and using that to select.

LOAD *,

If(IsNull(GiftID),1,0) As NullGiftID;

[#7]

Thank you, Antonio. The second solution worked for me. I still could not get the NullAsValue or NullValue options to work. I was using both lines cited multiple times in this thread. A flag of just null wasn't appropriate in this case because I wanted to select both null and other values for the field.

I did figure out that I can filter on the <null> in the table itself but not when I used a filter pane. In the filter pane no option for "<null>" appeared after using the length/trim statement.

Thank you to everyone who offered help.

[Lauri]

Hi Brian,

I was just having the same experience -- NullAsValue seeming to not work. I am now fairly certain that it's because my measure is counting an ID that is loaded from other sources, so I have IDs that have no related values in the dimension that has the annoying nulls.

Are you doing the following LOAD in a subsequent load statement? I suspect that would in effect "clean up" the nulls that weren't caught in the initial load.

LOAD *,

If(IsNull(GiftID),1,0) As NullGiftID;