Results 1 to 3 of 3

Thread: Splitting column data

  1. #1

    Default 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
    Last edited by mw9286; 07-08-2018 at 09:57 PM.

  2. #2
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    245

    Default

    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
    Last edited by awilliams1024; 07-09-2018 at 09:30 AM.

  3. #3
    Contributor
    Join Date
    Apr 2011
    Location
    Copenhagen
    Posts
    144

    Default

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •