Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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
Farol BI
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
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
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],' ');

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
Farol BI
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.