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

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

Mon, 15 Jun 2015 12:08:19 GMT

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

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

marcus_sommer — Mon, 15 Jun 2015 12:11:38 GMT

You could use wildmatch() for this.

- Marcus

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

buzzy996 — Mon, 15 Jun 2015 12:14:46 GMT

u can go with either,

match()

mixmatch()

if u have wild chart's u can go with

wildmatch() funtion.

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

ashfaq_haseeb — Mon, 15 Jun 2015 12:14:58 GMT

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

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

Mon, 15 Jun 2015 12:17:50 GMT

wildmatch() I think is best.

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

ganeshreddy — Mon, 15 Jun 2015 12:19:21 GMT

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.

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

petter — Mon, 15 Jun 2015 12:19:26 GMT

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;

LOAD

email Like '*@*' AS IsEmail

.....;

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

Tue, 16 Jun 2015 05:53:07 GMT

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.

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

Tue, 16 Jun 2015 05:59:02 GMT

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

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

PrashantSangle — Tue, 16 Jun 2015 06:04:57 GMT

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

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

Tue, 16 Jun 2015 06:45:44 GMT

Worked like a charm!

Thanks for all the help. Much obliged