QlikView TRIM function - need help testing

[#1]

Greetings, all!

I'm brand new to QlikView, so I apologize for this elementary question.  I'm trying to test the QV TRIM function before I employ it for a more elaborate process.  I understand how TRIM works in Excel, T-SQL, etc, so, in my mind, it should do something similar in QV.  However, I'm having an extremely difficult time even testing it!

In the code below, you can see that I've created an inline table with the "F1" value in the first record containing lots of trailing spaces (the spaces before the comma delimiting the values).  The code runs fine.  But when I spin off the results of the two tables into table boxes, the "F1" value seems to be already TRIMmed.  In other words, I'm not seeing the trailing spaces in the output either before or after the TRIM.

TestTable01:

LOAD * INLINE [

F1, F2
Trim Test    , 1
];

TestTable02:

LOAD

TRIM(F1) AS Trimmed

RESIDENT
TestTable01;

What am I doing wrong here? 

Thanks in advance for your help!

Joel

[oscar_ortiz]

You can try something like this:

TestTable01:

LOAD * INLINE [

F1, F2

Trim Test    , 1

];

TestTable02:

LOAD

*,

TRIM(NewValue) as NewTrimmed

;

LOAD

F1 & Repeat( ' ', 5 ) as NewValue,

TRIM(F1) AS Trimmed

RESIDENT

TestTable01;

[marcus_sommer]

To include the spaces within the inline-load you need to wrap them within quotes like:

...

"Trim Test    ", 1

...

Further trim() won't remove all types of space-chars or other not visible chars. Quite helpful are in these cases the use of keepchar() and purgechar() to control this definitely.

- Marcus

[swuehl · Accepted solution]

One thing to keep in mind, QV uses some automatisms, like trimming values, automatic number interpretations - sometimes useful, sometimes not.

W.r.t. the automatic trimming & in your setting, you can disable it using VERBATIM system variable:

SET Verbatim =1;

TestTable01:

LOAD * INLINE [

F1, F2

Trim Test    , 1

];

TestTable02:

LOAD

TRIM(F1) AS Trimmed

RESIDENT

TestTable01;

[#5]

Thank you so much for your help!  I had no idea there was such a thing as "SET Verbatim"!  But that allowed my test to work properly.

Thanks again!

Joel

[#6]

Marcus, thank you for your help!  While I needed the "SET Verbatim" command as shown in another answer, I would like to know more about the PURGECHAR() function.  I'm guessing that function would remove all spaces in a string, not just the "extra" ones?

Joel

[swuehl]

Joel,

purgechar() will remove the character regardless its position, so if you just want to remove leading and trailing white spaces, you can do it maybe using a Trim(Replace() ) combination.

See

Re: Qlikview script function to remove leading spaces at the end

[marcus_sommer]

Keepchar() could be imagined as a white-list and is especially useful if you could clear determine which char is a valid char (for example by only numbers). Purgechar() instead is the opposite from keepchar() and could be considered as a black-list.

But are the requirements to your check-conditions more complex you will probably need to combine them with other string-functions, like in the link from swuehl above.

- Marcus

[#9]

That's a very helpful insight.  Thanks, Marcus!

Joel