Splitting column data

[mw9286]

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

[awilliams1024]

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.

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

[ThomasT]

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

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