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.
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.