2 Replies Latest reply: Apr 15, 2014 4:10 PM by Stefan Wühl RSS

    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.

      PurgeChar_sort.png

      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!