Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Difficulty with NULLASVALUE

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

1 Solution

Accepted Solutions
antoniotiman
Not applicable

Re: Difficulty with NULLASVALUE

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

7 Replies
sunny_talwar
Not applicable

Re: Difficulty with NULLASVALUE

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;

Not applicable

Re: Difficulty with NULLASVALUE

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
Not applicable

Re: Difficulty with NULLASVALUE

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

antoniotiman
Not applicable

Re: Difficulty with NULLASVALUE

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
Not applicable

Re: Difficulty with NULLASVALUE

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;

Not applicable

Re: Difficulty with NULLASVALUE

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.

laurischarf
Not applicable

Re: Difficulty with NULLASVALUE

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;