Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
Hi,
thanks for the helpful reply.
I'm getting an error somewhere;
The "debug" function says :
Garbage after statement
NoConcatenate
CleanCustomerRecords:
Still having trouble with this
Anyone have any ideas?
Thanks