topic Select data that does NOT fall within a query range in QlikView

Select data that does NOT fall within a query range

Wed, 10 Jun 2015 05:00:17 GMT

Greetings.

I created a script which selected ONLY those data which meet a certain criteria.

In my case, the criteria were mainly a "clean-up" process --- meaning : removing all fields with empty rows, garbage data, etc, etc

(Thanks to those of you who helped me greatly with my previous post. Truly appreciate it

Now that I have successfully "cleaned" my data, and stored it in a sheet, I need to retrieve the "unclean" data.

Meaning, those data that were NOT clean (the data which I excluded from my LOAD script).

In normal SQL, this would be as easy as saying :

"Select * from xxxxx where data_id NOT IN.........(select "clean data"....)" , etc, etc

However, when I tried it in the QlikView script, I kept getting syntax errors.

(As a newcomer to QlikView, I've already learned that SQL query functions are not exactly the same as they are in QlikView. Certain operators/funtions need to be changed/altered in certain ways).

The trouble is : using Google to find those "certain ways" is not giving me any answers.

Here is my LOAD script (the one I used to "clean" and "purge" garbage-data and empty spaces :

CUSTOMERS:

LOAD

lastName,

firstName,

streetAddress,

phone,

postalCode

purgechar(lastName, '\''~!-´@#$%^&*()\?/<>,.;:_§|+[]{}%¨^~1234567890') as lastName_Clean,

purgechar(firstName, '\''~!-´@#$%^&*()\?/<>,.;:§_|[]{}%¨+^~1234567890') as firstName_Clean,

purgechar(streetAddress, '\''~!-´@#$%^&*()\?/<>;:§_|[]{}%¨+^~') as streetAddress_Clean,

purgechar(phone, '\''~!-´@#$€%^&*()\?/<>,.;:_§|+[]{}%¨^~') as phone_Clean,

purgechar(postalCode, '\''~!-´@#$%^&*()\?/<>,.;:§_|[]{}%¨+^~abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ') as postalCode_Clean

where len(trim(purgechar(streetAddress, chr(160)))) > 5 and

len(trim(purgechar(postalCode, chr(160)))) = 5 and postalCode <> '00000' and NOT IsNull(postalCode) and postalCode > '' and

len(trim(purgechar(postalOffice, chr(160)))) > 2 and

len(trim(purgechar(email, chr(160)))) > 5 and

len(trim(purgechar(firstName, chr(160)))) > 1 and

len(trim(purgechar(lastName, chr(160)))) > 1;

SQL SELECT *

FROM CoreBOA.dbo.Customer;

Now, I need to get those data which I "threw into the garbage-bin".............the data which falls OUTSIDE the range of this script.

Re: Select data that does NOT fall within a query range

PrashantSangle — Wed, 10 Jun 2015 05:16:14 GMT

Hi,

Do you have any primary Key field which gives unique row if yes then

You can use not Exist()

like,

CleanRecordScript:

Load PRimaryKey as CleanPrimaryKeyField,

...

from tableName;

NoConcatenate

UncleanRecords:

Load PrimaryKeyField as UncleanPrimaryKeyField

from tableName where not exists(CleanPrimaryKeyField,PrimaryKeyField);

regards

Re: Select data that does NOT fall within a query range

Wed, 10 Jun 2015 05:23:01 GMT

Hi Max_dreamer,

thanks for your response.

Sorry, I might need some clarification.

Yes, I do have a primary key; it is "customerID".

But, should I add your code to my already-existing script (above) ? Please, could you use my script as a template?

(the "CleanRecordScript" confuses me a bit

Re: Select data that does NOT fall within a query range

PrashantSangle — Wed, 10 Jun 2015 05:34:50 GMT

Hi,

Try like

Customers:

SQL SELECT *

FROM CoreBOA.dbo.Customer;

NoConcatenate

CleanCustomerRecords:

LOAD customerID as CleanCustomerID,

lastName as CRLastName,

firstName as CRfirstName,

streetAddress as CRstreetAddress,

phone as CRPhone,

postalCode as CRPostalCode,

purgechar(lastName, '\''~!-´@#$%^&*()\?/<>,.;:_§|+[]{}%¨^~1234567890') as lastName_Clean,

purgechar(firstName, '\''~!-´@#$%^&*()\?/<>,.;:§_|[]{}%¨+^~1234567890') as firstName_Clean,

purgechar(streetAddress, '\''~!-´@#$%^&*()\?/<>;:§_|[]{}%¨+^~') as streetAddress_Clean,

purgechar(phone, '\''~!-´@#$€%^&*()\?/<>,.;:_§|+[]{}%¨^~') as phone_Clean,

purgechar(postalCode, '\''~!-´@#$%^&*()\?/<>,.;:§_|[]{}%¨+^~abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ') as postalCode_Clean

where len(trim(purgechar(streetAddress, chr(160)))) > 5 and

len(trim(purgechar(postalCode, chr(160)))) = 5 and postalCode <> '00000' and NOT IsNull(postalCode) and postalCode > '' and

len(trim(purgechar(postalOffice, chr(160)))) > 2 and

len(trim(purgechar(email, chr(160)))) > 5 and

len(trim(purgechar(firstName, chr(160)))) > 1 and

len(trim(purgechar(lastName, chr(160)))) > 1

Resident Customers;

NoConcatenate

NotCleanRecords:

LOAD *

Resident Customers

where not exists(CleanCustomerID,CustomerID)

Drop table Customers;

Regards

Re: Select data that does NOT fall within a query range

Wed, 10 Jun 2015 06:11:51 GMT

Hi,

thanks for the helpful reply.

I'm getting an error somewhere;

The "debug" function says :

Garbage after statement

NoConcatenate

CleanCustomerRecords:

Re: Select data that does NOT fall within a query range

Thu, 11 Jun 2015 05:20:10 GMT

Still having trouble with this

Anyone have any ideas?

Thanks