I got into the habit of using Trim() in my load scripts for text fields, but I just stumbled across this page about the Verbatim system variable, which states:
Verbatim
Normally all field values are automatically stripped of leading and trailing blanks (ASCII 32) before being loaded into the Qlik Sense database. Setting this variable to 1 suspends the stripping of blanks.
So now I'm wondering when Trim() is useful vs redundant. I don't know when data is "loaded into the Qlik Sense database," so I am wondering:
- If I loaded a table and then reloaded it using Resident, would the values in that already-loaded table have been trimmed by that point?
- What if I made use of preceding loads? Would columns get trimmed between each load?
- What if I joined one table to another? Would the key columns that were used to do the join get trimmed automatically prior to the joining operation?
- I assume if I wanted to convert nulls, zero-length strings, and whitespace-only strings to some value, I would still have to do this kind of thing when loading the field (as opposed to leaving the Trim() out of it), right? If(Len(Trim(myField)), myField, 'NONE') AS myField
