Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
Difficult to say unless we can see it or test it out
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
According to Help...
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;
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.
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;