Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to remove data which contains "garbage" ?

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


1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like this

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

from datasource;

or U can try with KeepChar() function also.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

34 Replies
MayilVahanan

Hi

Try like this

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

from datasource;

or U can try with KeepChar() function also.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Thanks, Mayil.

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

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

MayilVahanan

hi

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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

MayilVahanan

HI

Can you please provide the sample data?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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

'

-

''

--

---

----

-----

------

-------

--------

---------

----------

-----------

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

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

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

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

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

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

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

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

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

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

''-...

-*

-.

-.---

-..

-§-

-a

-aaa

-aaaaa

-antthony

-Barbro

Not applicable
Author

Hi

and a copy of your script

Chris

Not applicable
Author

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? 



sgrice
Partner - Creator II
Partner - Creator II

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