topic How to exlude string from load statement? in QlikView

How to exlude string from load statement?

Tue, 05 Feb 2013 19:43:39 GMT

How do I exclude anything that contains "abcd.com" email from a list of emails? This is what I tried and it's not working...

User:

LOAD

"User_Key" AS [User Key FET],

"User_Id" AS [User ID],

"Account_Key" AS [Account Key],

Name AS [User Email Address],

"First_Name" AS [User First Name],

"Last_Name" AS [User Last Name],

"Role_Key" AS [User Role];

SQL SELECT *

FROM "Website_Usage_DW".dbo."Dim_User"

where Name <> '%abcd.com%';

Re: How to exlude string from load statement?

Tue, 05 Feb 2013 20:12:15 GMT

Have you tried Wildmatch?

WHERE WILDMATCH(Name,'*abcd.com*')=0;

Re: How to exlude string from load statement?

Tue, 05 Feb 2013 20:51:31 GMT

Unfortunately this did not work for me. Maybe where not exists can help? Would that apply or is that just for joining tables together?

Re: How to exlude string from load statement?

Tue, 05 Feb 2013 22:24:22 GMT

The guys on here may have a better suggestion then, but here is what I got to work. Load all your data in one table, then use the Where Wildmatch() as I suggested to keep only the records that don't contain '@abcd.com' in a new table using NoConcatenate, then drop the old table. See attached.

Re: How to exlude string from load statement?

rbecher — Tue, 05 Feb 2013 22:46:51 GMT

Hi all,

Wildmatch() should work on QlikView LOAD level. If you want to filter on database level (SQL SELECT) you should use NOT LIKE:

SQL SELECT *

FROM "Website_Usage_DW".dbo."Dim_User"

where Name NOT LIKE '%abcd.com%';

On a QlikView LOAD level this would be a preceding LOAD with a Where Clause as Rebecca suggested:

LOAD * WHERE WILDMATCH(Name,'*abcd.com*')=0;

SQL SELECT *

FROM "Website_Usage_DW".dbo."Dim_User";

But consider that in the second way all records gets passed to QlikView before filtered out. So, I'd suggest the database level filtering.

- Ralf

Re: How to exlude string from load statement?

rbecher — Tue, 05 Feb 2013 23:40:03 GMT

There is one small detail I messed up here: the trailing wildcard is probably not needed or wrong:

SQL SELECT *

FROM "Website_Usage_DW".dbo."Dim_User"

where Name NOT LIKE '%abcd.com';

LOAD * WHERE WILDMATCH(Name,'*abcd.com')=0;

SQL SELECT *

FROM "Website_Usage_DW".dbo."Dim_User";

- Ralf

Re: How to exlude string from load statement?

Thu, 07 Feb 2013 04:06:24 GMT

Thank you Rebecca and Ralf! I see the my error. I put Wildmatch in the from statement not the load statement. You guys are fantastic!!

Suzan