Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Giselle100
New Contributor II

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

2 Solutions

Accepted Solutions
thiago_justen
Valued Contributor III

Re: Remove null values and blanks cells

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
manoj217
Contributor III

Re: Remove null values and blanks cells

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

8 Replies
thiago_justen
Valued Contributor III

Re: Remove null values and blanks cells

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
manoj217
Contributor III

Re: Remove null values and blanks cells

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
New Contributor II

Re: Remove null values and blanks cells

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
Valued Contributor III

Re: Remove null values and blanks cells

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
New Contributor II

Re: Remove null values and blanks cells

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
Contributor III

Re: Remove null values and blanks cells

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
Contributor III

Re: Remove null values and blanks cells

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
New Contributor II

Re: Remove null values and blanks cells

Hi

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