Qlik Community

New to Qlik Sense

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Giselle100
Contributor III
Contributor III

Remove null values and blanks cells

My script to remove null values from the data is not working

 

Transaction:
Load
[Refund Qty],
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary
Where not IsNull([Refund Qty]);

 

please help

Labels (2)
2 Solutions

Accepted Solutions
thiago_justen

What about this?

Transaction:
Load
[Refund Qty],
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary
Where [Refund Qty]>0 or Not Match([Refund Qty],'-');

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

View solution in original post

manoj217
Creator III
Creator III

Transaction:
Load
[Refund Qty],
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary
Where not isnull[Refund Qty] or Not Match([Refund Qty],' ');

View solution in original post

8 Replies
thiago_justen

What about this?

Transaction:
Load
[Refund Qty],
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary
Where [Refund Qty]>0 or Not Match([Refund Qty],'-');

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

View solution in original post

manoj217
Creator III
Creator III

Transaction:
Load
[Refund Qty],
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary
Where not isnull[Refund Qty] or Not Match([Refund Qty],' ');

View solution in original post

Giselle100
Contributor III
Contributor III
Author

This worked but...

So my intention with this was that it would only return data up and until today with the year as at today.
It has removed the blanks from the years but there is stil one line of 2020 with a - and one line of 2021 with a dash.
How do i remove 2020 and 2021?
thiago_justen

You could also do the following:

 

Transaction:
Load
[Refund Qty],
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary
Where [Refund Qty]>0 or Not Match([Refund Qty],'-') and Year(DATE_FIELD)<2020;

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Giselle100
Contributor III
Contributor III
Author

Hi

 

That solution did not help. 

Is there no way that I can completely delete the null and blank fields as it is still displaying in the data. 

In this way it will remove the year(2020 and 2021) automatically.

manoj217
Creator III
Creator III

You want to remove nulls and blanks from the data itself right and may i know in which field you are getting this year.

can you please share the mock data based on that we will try to give some solution

manoj217
Creator III
Creator III

Transaction:
Load
if(length([Refund Qty])>0,[Refund Qty],if([Refund Qty]=' ','NA',if(isnull[Refund Qty],'NA',[Refund Qty]))),
[Refund Reason No] as [Refund Reason Number],
[Refund Reason Desc] as [Refund Reason Description]
Resident Summary

this condition will help u to find the null and space character

 

 

 

 

Giselle100
Contributor III
Contributor III
Author

Hi

The "if" expression is coming up as an error.