Query in QlikView script similar to SQL's "like" function.......

[#1]

Hi.

I have a couple of questions :

(1)  is there a function in QlikView which is similar to SQL's  "like" function.

For example :  I am trying to filter-out valid emails from the "Email" field in my database.

In SQL, it would be as easy as saying :  "Select * from my_customers where email like  '%@%';

(This makes sure that the email contains the symbol :  @  )

Not surprisingly, this does not work in QlikView's script.

How can I accomplish the same thing here?

(2)  the second part of my problem is to create a separate column in QlikView, which would contain boolean values "TRUE" or "FALSE", depending on whether or not the individual has a valid email.

For example :  if the script is loaded, and an individual has a valid email,  the string "TRUE" will be input into this separate field.

If the email is invalid  (for example, does not contain the symbol '@'),  then the value "FALSE" will be assigned to this separate field

Thanks

[marcus_sommer]

You could use wildmatch() for this.

- Marcus

[buzzy996]

u can go with either,

match()

mixmatch()

if u have wild chart's u can go with

wildmatch() funtion.

[ashfaq_haseeb]

HI,

Hope this helps.

Have a look at attached documents

Directory;
LOAD Email,
if(WildMatch(Email,'*@*'),'True','False') as Flag
FROM
Mail.xlsx
(ooxml, embedded labels, table is Sheet1);
//where WildMatch(Email,'*@*');

Regards

ASHFAQ

[#5]

wildmatch() I think is best.

[ganeshreddy]

Hi Stephen ,

you can try this as transformation   

if(Index(Email,'@')=0 or Isnull(Email),0,Email) as Email

(or)

put where clause to filter out data like below

where index(Email,'@') <> 0

Cheers,

Ganesh.

[petter]

You can use the LIKE both in a WHERE part of a LOAD statement and also use it in other contexts as a comparison operator. But the QlikView LIKE use * as a wildcard equivalent to a % in SQL.

It is classified as a string operator and it is hard to find in the QlikView Help function oddly enough:

________________________________________________________________________________________

String Operators

There are two string operators. One uses the string values of the operands and return a string as result. The other one compares the operands and returns a boolean value to indicate match.

&	String concatenation. The operation returns a text string, that consists of the two operand strings, one after another.
	Example: 'abc' & 'xyz' returns 'abcxyz'

like	String comparison with wildcard characters. The operation returns a boolean true (-1) if the string before the operator is matched by the string after the operator. The second string may contain the wildcard characters * (any number of arbitrary characters) or ? (one arbitrary character).
	Examples: 'abc' like 'a*' returns true (-1) 'abcd' like 'a?c*' returns true (-1) 'abc' like 'a??bc' returns false (0)

LOAD

          *

WHERE

     email Like '*@*';

SQL

     SELECT

          *

      FROM

          XYZ;

OR

LOAD

      *,

       email Like '*@*' AS IsEmail

.....;

[#8]

Thank you all for your very helpful replies.

I used the (*) symbol, and it worked great!

But, I was unable to assign the Boolean values of "True" or "False" to separate those people who have valid emails  (containing @),  and those who don't. 

[#9]

Thanks for your reply,  Ashfaq

I am loading data from a database.

I need to combine this query :

LOAD (email like '*@*') AS Person_Has_Email   (for example)

with this query :

IF EMAIL LIKE '*@*'...............Person_Has_Email = "True"................else Person_Has_Email = "False"

I have resolved the first part;  but, I don't want to create separate fields for those who have valid emails, and those who don't.  I want to create one field, and assign either TRUE or FALSE..............based on the query for valid emails.

Thanks

[PrashantSangle · Accepted solution]

Hi,

Create New field

try like

if(wildmatch(email,'*@*'),'True','False') as Valid_Mail_flag

Use Valid_Mail_flag in front end to get all valid / Invalid mail id.

Regards