Select data that does NOT fall within a query range

[#1]

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.

[PrashantSangle]

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

[#3]

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 

[PrashantSangle]

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

[#5]

Hi,

thanks for the helpful reply.

I'm getting an error somewhere;

The "debug" function says :

Garbage after statement

NoConcatenate

CleanCustomerRecords:

[#6]

Still having trouble with this 

Anyone have any ideas?

Thanks