Splitting column data

Hello:

I have a column with multiple values separated by commas that I would like to output to individual rows.

Example:

ACCT_ID, DATE, PRODUCT
123456789 2018-01-01 BMW,FORD,HONDA
121212121 2018-01-03 LEXUS
343435555 2018-03-19 CHEVY,DODGE

Desired output:

ACCT_ID DATE PRODUCT
123456789 2018-01-01 BMW
123456789 2018-01-01 FORD
123456789 2018-01-01 HONDA
121212121 2018-01-03 LEXUS
343435555 2018-03-19 CHEVY,DODGE



Thank you

Hi,

this should do what you want. I assume the last desired data line in your example should also have been split into two output records.

Code:

---

node:Static_Data
bretype:core::Static Data
editor:sortkey=5b431b61003224b7
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ACCT_ID,DATE,PRODUCT
123456789,2018-01-01,"BMW,FORD,HONDA"
121212121,2018-01-03,LEXUS
343435555,2018-03-19,"CHEVY,DODGE"

EOX
editor:XY=290,140
end:Static_Data

node:Filter
bretype:core::Filter
editor:sortkey=5b431bc278dd7d81
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
       
#Define the output - required prior to using do output
output 1 {

        #Set the output metadata for all of the input fields
        #but don't output the values yet
        emit * where false
}


#Split the 'Product' field on commas
Product_List = split(PRODUCT, ",")

#Now, loop through each element in the list
i = 0
while (i < len(Product_List)) {
       
        thisElement = Product_List.getItem(i)
   
        #Each time, define to write a record to output 1
        do output 1 {

                #Write the list element as the 'Product' field's value
               
                emit thisElement as "Product" 
               
        }
        i = i + 1
}
                       

EOX
editor:XY=450,140
end:Filter

---

You can also use the built in "Expand From List" node.

Quote:

---

node:Expand_From_List
bretype:core::Expand From List
editor:sortkey=5b4333931ebc34eb
input:@3ef3761c60777673/=Static_Data.40fe6c55598828e5
output:@3ed3a410684c59b3/=
prop:Delimiter=","
prop:ItemField=PRODUCT
prop:ListExpr=PRODUCT
editor:XY=330,520
end:Expand_From_List

node:Static_Data
bretype:core::Static Data
editor:sortkey=5b431b61003224b7
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ACCT_ID,DATE,PRODUCT
123456789,2018-01-01,"BMW,FORD,HONDA"
121212121,2018-01-03,LEXUS
343435555,2018-03-19,"CHEVY,DODGE"

EOX
editor:XY=240,520
end:Static_Data

---