Controlling the results of KeepChar() with regards to sort and distinct

[evan_kurowski]

Greetings QV Community,

Let's say in my QlikView script I am creating a flat file that is the output of a registry command

EXECUTE

CMD.EXE /c REG QUERY HKLM\SOFTWARE\ORACLE\HOME4 /v TNS_ADMIN > C:\_QlikView\TXT\Reglook4.txt;    

And the output looks something like below:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME4

    TNS_ADMIN    REG_SZ    P:\Oracle

I use the following to load Reglook4.txt into a single QlikView row (if there's a more efficient way to get this result loaded as a single row, please suggest)

[REGISTRY_RESULTS]:

LOAD @1:n AS ROW
FROM
C:\_QlikView\TXT\Reglook4.txt
(fix, codepage is 1252, header is 1 line, record is 2 lines)
WHERE LEN(Trim(@1:n)) > 0 ;

After this load I can create a list box, with an entry that looks like the following.. However, when I hover over the entry I see I have actually two lines of stored information.  So I expand to mult-line display and we can see the entire entry.

So at this point the field is multi-lined, but what I wanted to do was condense the whole field value, which is a single database row but multilined, into to a single database row, single lined.

What I attempted to do, and have had differing results with in the the past, is using the KeepChar() function to filter out all 'unwanted' characters and line breaks, etc.. from the cell entries.

So my 'Safe Character' string is a full listing of alpha-numeric, plus some acceptable programmatic characters (I'm hoping the line breaks and anything else "invisible" will be left behind, giving me all the pertinent information of this data on a single row).

[PURGED]:

NoConcatenate
LOAD KeepChar('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789:_/\',ROW) AS Purged
RESIDENT [REGISTRY_RESULTS];

In this case if I apply a KeepChar() function to the field ROW with a listing of 'Safe Characters' in the function argument, I'd expect (and have gotten in the past) a non-distinct/non-sorted filtered pass of the field.

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME4TNS_ADMINREG_SZP:\Oracle

Almost every time I've used KeepChar() this has been this case, however, in this instance it seems like the output of the KeepChar() is sorting my results alphabetically and also using a DISTINCT type of condensation of the results because the output was:

ACDEFGHIKLMNOPRSTWYZacelr4:\

I saw this occurring once before and just ignored it, but now seeing it again I'd like to understand where you can 'toggle' the sorting and distinct/non-distinct elements of the output of the KeepChar() function.  Does it matter under which context/circumstances/sources it's being used?  Can anyone give any assistance?  Much appreciated Qlik Community!

[Anonymous]

Hello Qlik Community Members- this discussion has was posted previously but accidentally deleted we are recreating the thread. If you have any helpful answers please feel free to respond.

[swuehl · Accepted solution]

Try

KeepChar(ROW, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789:_/\') AS Purged