Split a column based on carriage return.

Split a column based on carriage return.

With the current working of Split function from the extended data panel, you can split a column having String data type depending on a separator present in the string.

For example: A string xyz@company.com could be split into two columns with values 'xyz' and 'company.com' with the help of separator '@'.

But in situation, when you need to split a column which has multiple values in the same cell value separated by a new line or a tab as shown in the image:

Sample Data table

Column 1	Column 2
1	A B C
2	D E F

In this case, the separator would not be a specific character, rather it would be a new line or a tab value.

To be able to use this we will need to create a calculated column which will add a separator replacing the new line '\n' or a tab '\t' values in the string.

Create a calculated column as:

RXReplace([Column 2],"\n",".","g")

    OR

RXReplace([Column 2],"\t",".","g")

Here we have created/added a separator ".", which replaces the new line(\n) or a tab (\t) in the cell value for the Column 2.

Details on RXReplace()
Replaces a substring according to a regular expression. Search for the Arg2 regular expression in Arg1 and replace it with Arg3.
Arg4 specifies the options for the replacement: "g" specifies that if Arg2 matches more than once then all matches should be substituted.

Once the calculated column is created you can now use the default split() from the extended data panel and split using the separator "." and specify the number of columns. As shown in the below screenshot.


Sample DXP is attached.

Splitting a Column:

https://docs.tibco.com/pub/sfire-analyst/7.8.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/data/data_splitting_a_column.htm?_ga=2.62841643.624248738.1523873786-773253376.1502878466

Text Functions:

https://docs.tibco.com/pub/sfire-analyst/7.12.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_text_functions.htm