Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Scrubbing/Cleaning Data when Load

Hey All-

I've been scouring the QV discussions and a common theme came up: probably shouldn't use QV for data scrubbing purposes.

I get this, but unfortunately I'm stuck in a situation where QV is the only means (for right now) of accomplishing this task.  That being said, I have one field called CodeName that needs to be scrubbed and cleaned on a weekly basis as it's being loaded into QV.  I'd really rather not create a matchtable to do this because the CodeNames are constantly being created (hundreds on a daily basis), so a MatchTable isn't scalable.

Here are the common factors that need to be scrubbed:

CodeName

(front)code_123 --> changed to: code_123

(mainstore)  --> changed to: null

code_345(station) --> changed to: code_345

Also...  trim functions...  I'd like to cover any instance of a leading or trailing space?  Do I need to do both (e.g. trim( ' abc') and trim('abc ')?  or can I just encompasses everyting under one: trim(' abc ')? 

Thanks!

10 Replies
nagaiank
Specialist III
Specialist III

I have modified the load script to include scrubbing this pattern also. The added line in the script is

If(scrubbed_codename like 'code_???_*',Left(scrubbed_codename,8),scrubbed_codename) as scrubbed_codename

The modified file is attached.

Rob's suggestion also will work.

You will have to keep updating your scrubbing script as and when you find the unclean pattern in the data.