How to extract values from a column?

When working with textual data, you want to extract certain text from your data and create a new column for it. You can use the extract operation to perform that. Ideata analytics currently provides two ways of doing that:

Using suggestions on right panel

Step 1: When you create a new connection, you will get to Preview Screen.

Step 2: On preview screen, select the column in which you extract something. As and when you highlight some values within the column, you can get a list of suggested extraction which is possible on that column in suggestions tab of right panel.

Step 3 : Right Suggestion panel will give you options to extract on, from or between particular position, characters etc. When you hover on these options, You will get a preview of the possible output adjacent to the column in light yellow color.

Step 4: You can select the one which you like and click on it. It will apply the selected extract operation on the column and will create a new column by name "extract" for the result . You can rename the column as desired.

Using drop down transform list

Step 1: When you create a new connection, you will get to Preview Screen.

Step 2: On preview screen, select the column in which you want to extract a substring and click on down arrow.

Step 3: Click on "subString" from the drop down list in transform section. It will open up a pop up window for extracting subString

Step 4: Fill the required details as listed below and click "Apply"

  • start position: The position you want to start the extracted string

  • end position: The position till you want the substring

Step 5: Once you click on Apply, the system will apply the subString operation on data considering the postion selected and data will be extracted in a new column accordingly.