topic Re: How to remove data which contains "garbage" ? in QlikView

How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 07:43:06 GMT

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

Re: How to remove data which contains "garbage" ?

MayilVahanan — Thu, 04 Jun 2015 07:47:21 GMT

Try like this

Load *, purgechar(FieldName, '*?)=-') as FieldName

from datasource;

or U can try with KeepChar() function also.

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 07:52:47 GMT

Thanks, Mayil.

I assume that function should be inserted into the Main Script?

Or, inside the List-Box (as an expression) ?

Re: How to remove data which contains "garbage" ?

MayilVahanan — Thu, 04 Jun 2015 07:55:57 GMT

PurgeChar() function can able to use both in front end and script.

Suggestion:

You can creat a new field without unwanted characters and use in list box.

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 08:15:03 GMT

I tried this in the Main Script :

Load *, purgechar(lastName, '*?)=-') as lastName_Clean;

But, it did not create the new field : lastName_Clean.

So, I created a new field, in a new ListBox, and used this :

=purgechar(lastName, '*?)=-') as lastName

The expression was filled with errors.

So I removed the "as", and replaced it with a comma. Still errors

So I simply used this : purgechar(lastName, '*?)=-')

But, there was no effect. Still getting those garbage data

Re: How to remove data which contains "garbage" ?

MayilVahanan — Thu, 04 Jun 2015 08:22:11 GMT

Can you please provide the sample data?

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 08:29:09 GMT

Here is an example of what my LasName field looks like (I selected the top 30 values)

---

----

-----

------

-------

--------

---------

----------

-----------

------------

-------------

--------------

---------------

----------------

-----------------

------------------

-------------------

--------------------

---------------------------------

''-...

-.---

-..

-§

-§-

-a

-aaa

-aaaaa

-antthony

-Barbro

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 08:29:47 GMT

and a copy of your script

Chris

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 08:38:11 GMT

Mayil : it worked now

purgechar(lastName, '*?)=-') as lastName_Clean;

No idea why it didn't work earlier;

But, I got rid of all the garbage characters!

Thanks !!!

I have a question though : what can i add to the "purgechar" function, to remove NUMBERS as well?

Re: How to remove data which contains "garbage" ?

sgrice — Thu, 04 Jun 2015 08:46:28 GMT

purgechar(lastName, '*?)=-0123456789') as lastName_Clean;

Re: How to remove data which contains "garbage" ?

MayilVahanan — Thu, 04 Jun 2015 09:10:23 GMT

Whatever u need to remove, u can add in the second parameters.

purgechar(lastName, '*?)=-0123456789!@#$%^&*(') as lastName_Clean;

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 09:21:58 GMT

Hi Mayil,

thanks again.

How do I include "spaces"?

Meaning : how to remove blank spaces from the lastname field?

I tried to combine it with the expression I created earlier : if (Len(lastname_Clean) > 0, lastname_Clean)

But, my ListBox now shows two separate columns

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 09:57:38 GMT

Hi,

use the below syntax for removing these characters;

purgechar([RM Party], '*?)=-''-...-§') as lastName_Clean

If you find any other symbol then mentioned above, add them in the formula and they wont be reflected.

Use it in the script that would be better.

Nikhil

Re: How to remove data which contains "garbage" ?

MayilVahanan — Thu, 04 Jun 2015 09:57:52 GMT

Are you asking to remove blank spaces before or after the last name?

If so, use

Trim() function

purgechar(trim(lastName), '*?)=-0123456789!@#$%^&*(') as lastName_Clean

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 10:04:14 GMT

Hi Mayil,

sorry, I should have been more specific

I did not mean blank spaces before / after the lastname

I meant data-rows which are "blank" or "empty" (they contain nothing, because someone did not type in their last name)

Re: How to remove data which contains "garbage" ?

MayilVahanan — Thu, 04 Jun 2015 10:11:21 GMT

If you don't want to import those records in qlikview file means, try like this

Load * from datasource where Len(Trim(LastName)) > 0;

You can't able to delete that field value alone. You can make that empty space into null value like

If(Len(Trim(LastName)) > 0, purgechar(trim(lastName), '*?)=-0123456789!@#$%^&*(')) as lastName_Clean

Re: How to remove data which contains "garbage" ?

Thu, 04 Jun 2015 10:52:34 GMT

Hi Please follow below steps to remove all blank values from table.

In Scripting:

1.) PurgeChar([RM Party], '--------------''-...*-§') as [Clean Data]

It will give you below results;

2.) Create the chart with below expression:

=if(CleanName= '',0,CleanName)

This way you can remove all blank values....

I hope it will help you.

Regards

Re: How to remove data which contains "garbage" ?

Tue, 09 Jun 2015 06:32:51 GMT

I thank you ALL for your very helpful replies.

Unfortunately, I was unable to remove "empty fields".

No idea why.

But that is irrelevant now.

I have a last question : how do I remove apostrophes : ( ' ) ?

As you know, apostrophes are unique in SQL. I tried to include them in the PURGE-function, but, of course, I got a syntax error.

How can i include them in the function, so SQL knows that I am trying to purge them from my data?

Thanks again

Re: How to remove data which contains "garbage" ?

MayilVahanan — Tue, 09 Jun 2015 06:42:36 GMT

Try like this

=PurgeChar(FieldName,'\''~!@#$%^&*()\/<>,_+1234567890')

Re: How to remove data which contains "garbage" ?

Tue, 09 Jun 2015 06:53:51 GMT

Worked great, thanks

I'll keep working on the "empty fields"

I have a feeling that : the reason why "Len > 0" is not working, is probably because : those fields may be blank, but they are not empty, per se.

As you know, when somebody simply presses the "Space bar" on their keyboard, without entering any values, the field remains blank.............but it is no longer "empty"

I suspect that, this is why I am unable to remove those fields.