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.  Split Data in filed to Multiple Records

    Employee
    Posted 03-03-2017 17:56

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

    Originally posted by: Borg_species5618

    Hello All,

    I have one record where one field (Cust_Nbr) has multple bits of infomation sperated by a carriage return, I want to split the data so they become sperate records see below

    ---------------------------------
    --- One record contains -----
    ---------------------------------
    Cust_Nbr Region Country
    BB5364136 New York USA
    BB5364219
    BB5364273
    BB53630B7
    BB5363467
    BB5364502
    BB53639BB
    BB536B169
    BB5364053
    BB5364271
    BB5363062
    BB536463B
    BB5364639
    -------------------------------------------------------------------------------
    --- I would like the data split from One record to multple records -----
    --------------------------------------------------------------------------------

    Cust_Nbr Region Country
    BB5364136 New York USA
    BB5364219 New York USA
    BB5364273 New York USA
    BB53630B7 New York USA
    BB5363467 New York USA
    BB5364502 New York USA
    BB53639BB New York USA
    BB536B169 New York USA
    BB5364053 New York USA
    BB5364271 New York USA
    BB5363062 New York USA
    BB536463B New York USA
    BB5364639 New York USA

    I would appreciate any asistance people could give me

    Thankyou

    Frank


  • 2.  RE: Split Data in filed to Multiple Records

    Employee
    Posted 03-06-2017 06:28

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

    Originally posted by: gmullin

    See if the following works for you. I made a presumption that Customer Number is always 9 characters. And that a city could be 2 words like New York but a country would only be one word. I know that doesn't work for certain cases if your data is global so you might want to tweak that if you need to. Also I split it across one multiple filter nodes, but you could condense to one if you felt the need to make things faster.

    node:BRD_Data
bretype:core::Static Data
editor:Label=_RawData
editor:sortkey=58bd5e621a5f784c
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
_RawData:unicode
Cust_Nbr\tRegion Country\r\nBB5364136\tNew York USA\r\nBB5364219\t\r\nBB5364273\t\r\nBB53630B7\t\r\nBB5363467\t\r\nBB5364502\t\r\nBB53639BB\t\r\nBB536B169\t\r\nBB5364053\t\r\nBB5364271\t\r\nBB5363062\t\r\nBB536463B\t\r\nBB5364639\t
EOX
editor:XY=140,50
end:BRD_Data

node:Step_1__split_out_records
bretype:core::Filter
editor:Label=Step 1 - split out records
editor:sortkey=58bd5e7d6a8358d5
input:@40fd2c74167f1ca2/=BRD_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
myList = '_RawData'.split("\n")	# split on newline
i = 0


output 1 {

emit str(null) as DATA	# setup output pin
where false 
}


while i < myList.len() {

do output 1 {
emit myList[i].str() as DATA
}
i = i + 1

}
EOX
editor:XY=230,50
end:Step_1__split_out_records

node:Step_2__discard_first_line_with_fieldnames
bretype:core::Filter
editor:Label=Step 2 - discard first line with fieldnames
editor:sortkey=58bd6097304101fe
input:@40fd2c74167f1ca2/=Step_1__split_out_records.40fd2c7420761db6
output:@40fd2c7420761db6/=
prop:Script=<<EOX

emit *
where firstExec == false

EOX
editor:XY=330,50
end:Step_2__discard_first_line_with_fieldnames

node:Step_3__clean_up_data_for_field_names
bretype:core::Filter
editor:Label=Step 3 - clean up data for field names
editor:sortkey=58bd60d029913beb
input:@40fd2c74167f1ca2/=Step_2__discard_first_line_with_fieldnames.40fd2c7420761db6
output:@40fd2c7420761db6/=
prop:Script=<<EOX
if firstExec then {
	custNo = 'DATA'.substr(0,9)
	newData = 'DATA'.replace(custNo,"")	# remove customer number
	
	splitSpace = newData.split(" ")
	
	if splitSpace.len() == 2 then {   # if we split into 2, then we have City, Country
		city = splitSpace[0].str()
		country = splitSpace[1].str()
	}
	else if splitSpace.len() == 3 then {  # if split into 3 presume first 2 belong to city
		city = splitSpace[0] + " " + splitSpace[1]
		country = splitSpace[2]
	}
	
}


emit custNo as "Cust_Nbr", city as City, country as Country

EOX
editor:XY=440,50
end:Step_3__clean_up_data_for_field_names