topic Re: How to hide/remove rows which contain "garbage" values? in QlikView

How to hide/remove rows which contain "garbage" values?

Thu, 04 Jun 2015 07:20:31 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 hide/remove rows which contain "garbage" values?

robert_mika — Thu, 04 Jun 2015 07:56:58 GMT

Maybe:

Where WildMatch(field,'*-*');

Re: How to hide/remove rows which contain "garbage" values?

Thu, 04 Jun 2015 08:20:05 GMT

I put this in my Main Script :

WildMatch(lastName,'*-*') as lastName_clean;

The new field only gave 2 values : zero and one (0 and 1)

Re: How to hide/remove rows which contain "garbage" values?

robert_mika — Thu, 04 Jun 2015 11:27:31 GMT

Try like this:

t1:

LOAD * Inline [

f1, f2

A--a, 1

B, 2

C---, 3

D, 4

E, 5

F, 6

G, 7

H, 8

];

left join(t1)

LOAD

f1,

WildMatch(f1,'*-*') as lastName_clean

Resident t1

;

t2:

load

f1 as F1,

f2 as F2,

lastName_clean as lastName

resident t1

where lastName_clean=0;

drop Table t1;

A and C have been removed.

Re: How to hide/remove rows which contain "garbage" values?

Tue, 09 Jun 2015 06:34:23 GMT

Thank you 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 hide/remove rows which contain "garbage" values?

robert_mika — Tue, 09 Jun 2015 06:40:08 GMT

Instead of purge ' try chr(39).

This is ASCI code for single apostrophe

=PurgeChar('Robert''s',Chr(39))

(there is double ' not single " between "t" and "s" as single ' would throw an error.

Re: How to hide/remove rows which contain "garbage" values?

Tue, 09 Jun 2015 06:55:24 GMT

Thanks