SPLIT

SPLIT(data, integer[size] | character[split on character]);

 

Overview

• Used with Scatter to split 1 field into several records.
• Some databases has a big textfield for comments in a Customer record, whereas e.g. Navision has a separate table with several comment records for each Customer.
• To split the field Comment on a Customer record into a separate Comment table you need to setup a Scatter transfer on the Customer transfer.
• On the Scatter transfer you then use the split formula in the fieldlist.

##SPLIT("Comment",'; ')

If a Comment field in the source record has a syntax where each comment is separated with ; the split formula will make a record for each comment.

 

Example 1 (Example of a Scatter on a Customer transfer):

Source Record:

No.Comment

10008	'20082003 Credit Limit exceeded.; 23082003 Notification Sent.; 30082003 Account OK

Destination Record:

No.Line No.Comment Line

1008	1	20082003 Credit Limit exceeded.
1008	2	23082003 Notification Sent.
1008	3	30082003 Account OK

NOTICE THAT:

• The Field "Line No." is filled using the Auto Generate Key functionality on the Scatter transfer.
• To do this you simply mark the checkbox on the Scatter, and map the destination field in the Scatter link list. In the source field syntax must be ##'-1'. Refer to the setup below:

Scatter Setup:

Link List:

Source FieldDest. field

No.	No.
##'-1	Line No.

Field List:

Source FieldDest. field

No.	No.
##SPLIT("Comment",'; ')	Comment Line

 

Example 2:

##SPLIT("Location"+IF(EQUALS("location2",''),'',':'+"Location2"),':')

• This formula will split Location 1 and 2 into two records if both fields have a value. In case the Location2 field is empty, only one record would be created.
• If Location2 ='' then insert nothing after Value in Location. ('Location Value'+''
• If Location2 is not empty then insert (Location Value+':''Location2 value')
• If there is a value in the location2 field, a record is created where the values in location and location2 are separated by :.
• Split will then work on that record and split it into 2.