Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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
Highlighted

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

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.

View solution in original post

10 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

You could use wildmatch() for this.

- Marcus

Highlighted
buzzy996
Honored Contributor II

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

u can go with either,

match()

mixmatch()

if u have wild chart's u can go with

wildmatch() funtion.

Highlighted

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

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

Highlighted
Not applicable

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

wildmatch() I think is best.

Highlighted
ganeshreddy
Contributor III

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

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.

MVP
MVP

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

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

.....;



Highlighted
Not applicable

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

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. 

Highlighted
Not applicable

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

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

Highlighted

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

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.

View solution in original post