Remove null values and blanks cells

[Giselle100]

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

[Thiago_Justen_ · Accepted solution]

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],'-');

[manoj217 · Accepted solution]

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]

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;

[Giselle100]

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]

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]

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]

Hi

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