Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Vaishuqlik
Contributor
Contributor

Filtering Null/Unassociated Blanks

We have a Business Scenario like below where we need to filter Null/Unassociated Blanks in a Qlik report. Due to security reasons, showing a sample data scenario,

 

Table1:
LOAD * Inline
[
No,Name
1,Apple
2,Orange
3,Banana
];

Table2:
LOAD * Inline
[
No,Namelist
1,Apple
2,Orange

];

When a chart is plotted against above data,  "Namelist "column will have blanks /nulls for "No" column  for value  3 in a table box like below,

123.png

 

 

 

 

 

 

 

Problem statement is can we filter the blanks/Nulls in "Namelist" column in Qliksense by default?

 

Note: We want to see what "No" column values have blanks/nulls in "Namelist" column

 

 

5 Replies
Vegar
MVP
MVP

You could use the Qlik Sense nullasvalue feature.

NullAsValue - Qlik Sense

Vegar
MVP
MVP

Disregard my previous post. I looked once more at your script an notice that the empty namelist is not a null value in your data it is a missing value. 

You could solve this by using a join in the script or you could do nothing, but instead do a smart search to filter the empty namelist numbers. See gif animation below.

f378c0ad-0405-45c3-91cc-eb048b519e01.gif

 

 

- Vegar

Vaishuqlik
Contributor
Contributor
Author

Great! I think this helps but additionally if we want to filter nulls on Namelist column in addition with a value, do you have any suggestions for it as a smart filter expression?

Vegar
MVP
MVP

You need to generate null values in a table in order to give them an selectable value.

 

Try the script below. 

NullAsValue Namelist;
Set NullValue = 'NULL';


SET NULLINTERPRET= ''; //Needed for text and inline sources.
Table1:
LOAD * Inline
[
No,Name
1,Apple
2,Orange
3,Banana
];

LEFT JOIN 
LOAD * Inline
[
No,Namelist
1,Apple
2,Orange
];

//The null value for Namelist is firs READ and recieve the nullvalue in the load below.
Table:
NoConcatenate LOAD * Resident Table1;
DROP TABLE Table1;
trdandamudi
Master II
Master II

One way you can do is as below:

QlikSense-Q1.PNG

1. Create a table as shown above

2. In the field 'No' search box type : =isnull(Namelist) and select the appropriate rows.

3. Save this as a book mark "SelectNulls"

4. Create a button and assign the bookmark (SelectNulls) to the action.

5. Now anytime you want to see all the null records you can click on the button.

Hope this helps.