Follow

How to split a single field into multiple fields

Note: This old article cites a deprecated feature .  However, it may still be available in some later versions.  

Introduction

Sometimes you may need to split a singe field into multiple fields. You can do this by using the Split-function in the data movement process.

Scenario

We have a staging table that looks like the one below and we would like to split the fields using space as the separator.

split1.png

Step-by-step

  • Create a table on the DWH with two fields, "Column1" and "Column2". Drag the field “Column1” on stage to the field “Column1” on DWH to initiate the data movement. In the dialogue, select Split as the Method.

    split2.png

  • In the Add Split Field-dialogue, input the separator (we use space in this example), make sure the Source Field is correct and choose the Destination Fields

    Split3.png

  • The result on the data warehouse should look like this:

    Split4.png


This article applies to: timeXtender 4.5, tX2012, TX2014. Last updated: 2014-03-24.

Was this article helpful?
3 out of 3 found this helpful

4 Comments

  • 1
    Avatar
    Hansen, Mikkel Hecht DK - NOH

    Is there a similar solution for newer version of TimeXtender?

  • 0
    Avatar
    Padarthi, Satish

    I am not able to create this. Is this available now?

  • 0
    Avatar
    Joseph Treadwell

    Hi Satish & Mikkel, we deprecated this feature due to it's limited use case. We found it significantly more useful to solve this using field transformations. See below:

    Split Left:

    SUBSTRING([ValueToSplit],1,CHARINDEX(' ',[ValueToSplit])) 

    Split Right:

    SUBSTRING([ValueToSplit],CHARINDEX(' ',[ValueToSplit]),50)

    Edited by Joseph Treadwell
  • 1
    Avatar
    Alex Wijnen

    This is a perfect example for using snippets.

    I developed the Split function as a snippet and shared it here. You can easily compose these snippets, follow this link for more information.

Please sign in to leave a comment.