7 Replies Latest reply: Feb 28, 2013 3:43 AM by Anju Gopinath RSS

    Split data in SQL

    kalsakorn

      Hello everyone.

      I am Ann. I Started Learning to use qlikview.

      I have asked for the function Split.

       

      example:

      error loading image

      Can see that the data are separated " ; " I want to extract data from each row below.

      Result :

      error loading image


      this SQl :

      MMUsers:

      LOAD "MM_Approver",
      "MM_Company",
      "MM_IDUser",
      "MM_User";
      SQL SELECT "MM_Approver",
      "MM_Company",
      "MM_IDUser",
      "MM_User"

      Please help me to do that.

        • Split data in SQL
          Johannes Sunden

          Hi Ann,

          You can use the subfield() function for that. Please see the info from the F1-help:

          subfield(s, 'delimiter' [ , index ] )

          In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

          In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

          Examples:

          (For three parameters)

           

          subfield(S, ';' ,2)

          returns 'cde' if S is 'abc;cde;efg'

          subfield(S, ';' ,1)

          returns NULL if S is an empty string

          subfield(S, ';' ,1)

          returns an empty string if S is ';'



          • Split data in SQL
            s j

            Dear Kallaya,

            Qlikview have inbuilt feature to load data which is seperated by any character and you want upload it in qlikview.

            Steps

            1) Go to Script

            2) Clik On Data Tab

            3) Clik on Filed Data Button

            4) You can select you text file and delimited it by ;

            It Work fine in your case.

            Regards

            Sunil Jain.