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

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

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

1 Solution

Accepted Solutions
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

10 Replies
marcus_sommer

You could use wildmatch() for this.

- Marcus

buzzy996
Master II
Master II

u can go with either,

match()

mixmatch()

if u have wild chart's u can go with

wildmatch() funtion.

ashfaq_haseeb
Champion III
Champion III

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

Not applicable
Author

wildmatch() I think is best.

ganeshreddy
Creator III
Creator III

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
Partner - Champion III
Partner - Champion III

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'

   

likeString 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

.....;



Not applicable
Author

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. 

Not applicable
Author

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

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂