1. Grow
  2. Organizing Data
  3. Creating Datasets and Transforms

Using Extract Transform

Article last updated:

Sometimes, you might want to extract only a part of the information present in your column. There might be prefixes that you would want to trim; or you might want to split the data in multiple columns for better analysis.

This is where the Extract Transform comes in. Based on the extract transform type, you can trim any fixed length of a string from the data, or you can use regular expressions to extract the part that you want from the data, or extract only a part of a date field, etc.

Creating Extract Column

  1. From the data tab in the metric builder, select the Add Transform button, then select Extract.
  2. Enter a name for the new column in New Column Name field.
  3. Select the kind of extract operation you want, in the Extract Type field. Select the column from which you want extract data in the Column field.

    You can select an of the following extract types:

    1. Fixed Width: Use this to extract a fixed width substring from the original string. Enter the Starting Position and Total Characters in the respective fields. For instance, if you have "presales" and want to take out "sales", you can enter Starting Position as 4 and Total Characters as 4.
    2. Delimited: You can use this feature to extract strings where you have a delimiter. For example, if your original value has hyphen, you can use this extract type to extract the text that comes before or after the hyphen. Enter the Delimiter and the Desired Part (where 1 is the part before the delimiter and 2 is the part after the delimiter).
    3. Pattern Match: You can also use regex to extract a part of the information. The transform looks for the pattern you specify and extracts that part. Enter the details in the Regular Expression and Flags fields. For more information on regular expression, you can refer to the Using RegextExtract document.
    4. Left/Right: Use this type to split a string in two based on the position, and then extract the left or right part. For example, you have the value "presales", enter the Position as 3, From as Start, and Extract Part as Right, to get "sales" as the extracted value.
    5. Date Part: This type is used only for date and datetime datatype columns. Select the part of the date that you want, like, Year, or Month, etc. in the Date Part field. For example, you have a column which stores the closing date of your deals. If you want to extract the year from that data, choose this extract type, and enter Year in the Date Part field. So, for a closing date of 1st January 2023, you get "2023".
      Here are some of the sub-options that are available for Date Part:
      • DOW : It refers to the day of the week. The value ranges from 0-6; where, zero is Sunday.
      • DOY : It stands for the day of the year, with the value ranging from 1-365/366.
      • Epoch : It is the fixed date and time used as a reference from which a computer measures system time.
      • Julian : It returns the date in Julian format.
      • Isodow : It refers to the ISO-based day of week. Here, 7 represents Sunday while 1 represents Monday), unlike Dow.
      • Isoyear : ISO years always start on the first Monday closest to January 1. This means the year may start anywhere between December 29 and January 4. Most of the time, the year and isoyear line up.
      • Timezone : It is measured in seconds. The positive values correspond to time zones east of UTC, negative values to zones west of UTC.

        • You can refer to this article from PostgreSQL for more details.

    6. Truncate Date: This type is used only for date and datetime datatype columns. You can truncate, or round off the date information based on the precision. For example, you have a column Created Date for your deals, which automatically stores the date and time when a deal is created in the system. But you want to have the information only till the date that they are created. In this case, you can set the Precision to Date, and the information that is more granular than date (like the hours, minutes, seconds) will be discarded.
  4. Once you have added the columns to be considered for the transform, select Run and your new column will populate in the table of data.
  5. Select Save to save this report.

Was this article helpful?