4 Replies Latest reply: Nov 11, 2013 2:21 PM by Jean-Pierre Bakhache RSS

    QlikView TRIM() syntax question

    Robert Fishel

      Hello my fellow QlikView enthusiasts.  I have ran across an issue that I would like to understand better.  I have a little piece of code that works fine in QV Version 11.0.11440.0 SR2 64-bit Edition (x64) but does not work in QV Version 11.20.12129.0 64-bit Edition (x64).

       

      This is the code:

       

      LOAD

        RIGHT(TRIM(@1:n), 2) as State,

        TRIM(@1:n) as Data

      FROM

      [US States.txt]

      (fix, codepage is 1252);

       

      I am getting the little red error underlining at the colon between the 1 and the n in @1:n.

       

      Also, I don't understand what this code is doing.  I've never seen the @ symbol in a TRIM function.

       

      I found this code online.  I am trying to embed a map into a qvw. Thank you all.

        • Re: QlikView TRIM() syntax question
          Carlos Reyes

          Trim() only receives one string as parameter, so the " @1:n " must be the field name. Have you tried to change the field name?

           

          Perhaps... it is not allowed using a colon into a field name.

          • Re: QlikView TRIM() syntax question
            Jean-Pierre Bakhache

            Hi,

             

            The @1:n is not actually related to the trim function, rather to the fact that a text file is being loaded as fixed record.

            The n represents the number of characters used to split a field from another.

            The @1 represents field1 and so on (used instead of specifying a field name, for example: load @1 as CustomerGroup, @2 as CustomerName etc...).

             

            You can test with the attached files, try to load the dummy file Fix.txt, instead of "delimited", choose "fix record" then hover your mouse on the field contents (as shown in the image attached) and choose as many parts as you want.

            I also get the red error underlining, but the script works fine (11.20 sr4).

             

            Hope this helps

              • Re: QlikView TRIM() syntax question
                Robert Fishel

                Thank you so much for explaining this to me Jean-Pierre.  One last question; what does the "codepage is" portion mean?  Thank you.

                  • Re: Re: QlikView TRIM() syntax question
                    Jean-Pierre Bakhache

                    You're welcome

                     

                    As to your question, the codepage refers to the character set that should be used to read the file (how the file was encoded).

                     

                    I've attached another dummy file that I've saved using the utf8 character set (in the "save as" dialog, you can pick the encoding type).

                    As you'll see, the file contains characters with accents (in french: accent grave, aigu et circonflexe).

                    If you try loading the file and selecting "western european" as character set (which is referred to as "codepage is 1252"), you'll notice unreadable characters. However the last word "canon" can still be correctly read as it doesn't contain an accent.

                     

                    The same file, when saved using the "ansi" character set, can be read in qlikview with the "western european" set.

                     

                    The number after "codepage is" depends on which character set you're selecting from the list.

                    If you're selecting the "unicode" or "utf8", the syntax will change without "codepage is".

                     

                    For example this is the script to load the attached file:

                     

                    Data:

                    LOAD @1
                    FROM
                    fixutf8.txt
                    (
                    txt, utf8, no labels, delimiter is '\t', msq);

                     

                    Cheers!