Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Sure
Give me a sec
Is that the FirstName and email that need cleaning?
The email empty looks like Null to me.
Any chance to get sample of your data?
Yeah, I can dig it out from the DB.
I am also trying to validate EMAIL.
But, my script does not recognize this statement : ...............where email LIKE '%@%'
It gives me an error;
Also : IS NOT NULL gives me the same syntax error;
Oh, I found it :
WHERE NOT IsNull(field)
Still doesn't remove that empty space, though................hmmmmm