21 Replies Latest reply: Dec 18, 2014 8:46 AM by Paul Madden RSS

    Split a Delimited String

    Paul Madden

      Is there a way to split a single string into n number of rows?

       

      Let's say I have an incoming table with two fields: "PrimaryKey" and "Users".  "Users" could have any number of usernames in it delimited with a comma.  For example one record might look like this:

       

      "234" -- "JDOE, JSMITH, JUSER"

       

      I would like to split that up into three distinct records:

       

      "234" -- "JDOE"

      "234" -- "JSMITH"

      "234" -- "JUSER"

       

      Any ideas?  Thanks in advance!

        • Re: Split a Delimited String
          Bill Markham

          Have a look at the SubField() function.

          • Re: Split a Delimited String
            François Cavé

            Hi Paul,

            Use the SubField() function like below:

            SubField(Users,',',1) --> JDOE

            SubField(Users,',',2) --> JSMITH

             

            François

            • Re: Split a Delimited String
              Ruben Marin

              Edit: Sorry, I didn't see it was for expressor

               

              Hi Paul, as said above you can use subfield function, in example you can can use something like:

              LOAD PrimaryKey,

                   Subfield(Users, ',') as User

              FROM ...

              • Re: Split a Delimited String
                Piet Hein van der Stigchel

                Should you want a variable load, so without manually typing SubField 1 to a fixed n times, have a look at attached example

                • Re: Split a Delimited String
                  Peter Cammaert

                  Sorry, post deleted. Supplied a QLikView answer to an Expressor question. Not good...

                  • Re: Split a Delimited String
                    Kenneth Madsen

                    Perhaps an aggregate can be used to emit multiple records for the key.  You would loop in the aggregate function until no more values are found in the second column.

                      • Re: Split a Delimited String
                        Kenneth Madsen

                        I checked again in my email archives and found this from John Lifter:

                         

                        "To go from a single input row to multiple records, the transform operator is best.  The row comes in and then you use the iterative return to generate the multiple outputs.

                         

                        To go from multiple input records to a single row, the aggregate operator is best.  The common field value in each of the multiple records is the aggregating key.  As each record is processed, you add it to a lua table.  Then in the results function you iterate through the table and extract the various values and use them to initialize a single record."

                         

                         

                        Also, there are pivot operators that can do this.

                         

                          • Re: Split a Delimited String
                            Paul Madden

                            I'm more of a visual person. So I apologize if I am not explaining fully.  Take a look at the photos.

                             

                            I need to turn this:

                            12-16-2014 8-37-58 AM.jpg

                             

                            Into this:

                            12-16-2014 8-39-08 AM.jpg

                             

                            Considering this from Expressor, here is what needs to happen:

                             

                            1. Record ID # 40 comes in
                            2. [Note] field is examined for a delimiter (comma)
                            3. For each existing comma:
                              1. Duplicate the record
                              2. Retain the appropriate substring
                            4. Output the final recordset.

                             

                            I believe this is outside of Expressor's capabilities because I have no way of forecasting how many delimited strings will be found in the [Note] field.  Could be 1, could be 1000. If I knew there were never more than 3 (for example), I'm sure I could just use a Mutli-Transform to split the delimited [Note] field, then Pivot Row on the resulting recordset, and Filter empties. But I don't see how Expressor can do this dynamically without first being told how many demilited strings will be found in the [Note] field. In addition, the number of delimiters may very well go beyond the 10 outputs that Expressor Operators are limited to.

                              • Re: Split a Delimited String
                                Kenneth Madsen

                                How about this:

                                 

                                - MESSAGES-TRACE_EX-I: log: 40 John Doe Sales (.)

                                - MESSAGES-TRACE_EX-I: log: 40 John Doe Marketing (.)

                                - MESSAGES-TRACE_EX-I: log: 41 Jane Doe Security (.)

                                - MESSAGES-TRACE_EX-I: log: 42 Joe User Accounting (.)

                                - MESSAGES-TRACE_EX-I: log: 43 John Smith Accounting (.)

                                - MESSAGES-TRACE_EX-I: log: 43 John Smith Payroll (.)

                                - MESSAGES-TRACE_EX-I: log: 44 Jeff Jefferson Accounting (.)

                                - MESSAGES-TRACE_EX-I: log: 44 Jeff Jefferson Payroll (.)

                                - MESSAGES-TRACE_EX-I: log: 44 Jeff Jefferson Ad Hoc (.)

                                 

                                Produced with this in an Aggregate rule set to Iterative return:

                                 

                                function prepare(input)

                                   records = {}

                                   work = {}

                                end --prepare

                                 

                                function aggregate(input, index)

                                   startp = 1

                                   output = {}

                                   endp=ustring.find(input.Department, ",")

                                   if endp == nil then

                                     work[#records+1] = input.Department

                                     records[#records+1] = input

                                   else

                                     parse = true

                                     while parse do

                                       if endp == nil then

                                         fn = ustring.trim(ustring.substring(input.Department, startp))

                                         parse = false

                                       else

                                         fn = ustring.trim(ustring.substring(input.Department, startp, endp-1))

                                       end

                                       work[#records+1] = fn

                                       records[#records+1] = input

                                       startp = (endp or 0) +1

                                       endp=ustring.find(input.Department, ",",startp)     

                                     end -- while

                                   end -- if

                                end --aggregate  

                                 

                                  

                                function result(input, count)

                                   cnt = 0

                                   return function()

                                     cnt = cnt + 1

                                     if cnt <= #records then

                                       output = records[cnt]

                                       output.Department = work[cnt]

                                       return output

                                     else

                                       return nil -- no more records

                                     end -- if

                                   end -- iterative function

                                end --result


                                It is sort of using Aggregate to not aggregate.  You are supposed to be able to do this in a transform rule set to iterative, but there isnt a result function for the transform.  I tried initially as per the help, but didn't get it to work at first and I fell back to what worked for me.


                                Pivot operators won't work as it is for turning multiple columns into rows, not parsing a single column and making rows out of it.


                                I can give you a project export if you want.

                          • Re: Split a Delimited String
                            François Cavé

                            Hi Paul,

                            Can you mark a post as correct to close this issue?

                            Thanks,

                            BR,

                            François