How to remove data which contains "garbage" ?

[#1]

Hi,

I just started learning/using QlikView.

I built my first App, using data which I extracted directly from the database.

My problem is :  a lot of the data-fields contain "garbage".

For example, the "lastname" field contains stuff like :  "----", or "*?)=", etc

The point is :  some of the fields are either empty, or they contain rubbish data.  (Some of the data under "lastname" are even written as "373733".

Obviously, some people couldn't be bothered to spell-out their full names 

Nothing wrong with that, sure.

But, I want my List-Boxes to display only those data which are actually DATA.

I already found the solution to removing "empty" data-rows, by using the following expression in my List-Box (for last name)

if (Len(lastname) > 0, lastname)

But, how do I get rid of those other unwanted data?

I tried to solve the problem directly from the SQL Script (under "Main" -- Edit Script).  For example, to remove those "lastnames" which contain simple dashes ( - ), I wrote an SQL query, like the following :

Select * from my_table where lastname NOT LIKE '%-%';

But, this is not working.  No idea why.

Any way, it's much easier to simply write an expression directly inside the ListBox itself.

But, no idea what format the expression should take.

Thanks

[MayilVahanan]

Hi

You can't able to remove that field value alone.

Eg: If(Len(trim(Field))>0, Field) // Here its remove the null or empty field. But its goes to else part and again the value is null for that field.

You can remove the row itself. But am not sure, your requirement is like that

Load * from datasource where Len(trim(Field)) > 0;

Edit:

IF any answer helps means, mark it as helpful. Its helps someone to refer easily for similar problem.

[#3]

Actually, that is precisely what I want.

I want to remove those rows completely, where :  Len(trim(Field)) > 0 

So I guess I should add this to the main script

[#4]

I added it to the main script.

It removed some of the "empty" fields

But, still, not all  

[MayilVahanan]

Hi

Is it possible to share some sample data?

[robert_mika]

Try:

if( len(trim(purgechar(FIELD, chr(160)))

160 is no braking space charakter

[#7]

Hmm........please, could you check the code, Robert?

I'm getting "Errors in expression";

[#8]

Oh wait.

All's well.  I just needed an extra bracket at the end

[robert_mika]

len(trim(purgechar(FIELD, chr(160)))>0

[#10]

Funny thing, though :  the script worked, except for one thing :  EACH of the data fields has ONE remaining empty field   

Just one, per data-field.

I guess this is the best I'm going to get  (LOL).

[robert_mika]

Could you share print screen of this?

Maybe we can find a way to make it cleaner..