LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Splitting column data

    Employee
    Posted 07-08-2018 06:13

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: 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


  • 2.  RE: Splitting column data

    Employee
    Posted 07-09-2018 01:57

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: 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


  • 3.  RE: Splitting column data

    Employee
    Posted 07-09-2018 03:11

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: 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