topic Re: QlikView TRIM function - need help testing in QlikView

QlikView TRIM function - need help testing

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: QlikView TRIM function - need help testing

oscar_ortiz — Wed, 20 Apr 2016 17:28:31 GMT

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;

Re: QlikView TRIM function - need help testing

marcus_sommer — Wed, 20 Apr 2016 18:18:34 GMT

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

Re: QlikView TRIM function - need help testing

swuehl — Wed, 20 Apr 2016 18:36:08 GMT

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;

Re: QlikView TRIM function - need help testing

Thu, 21 Apr 2016 21:24:59 GMT

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

Re: QlikView TRIM function - need help testing

Thu, 21 Apr 2016 21:28:39 GMT

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

Re: QlikView TRIM function - need help testing

swuehl — Thu, 21 Apr 2016 21:58:30 GMT

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

Re: QlikView TRIM function - need help testing

marcus_sommer — Fri, 22 Apr 2016 05:45:39 GMT

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

Re: QlikView TRIM function - need help testing

Fri, 22 Apr 2016 15:12:59 GMT

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

Joel