7.6
  • What's New in the BDB 7.6?
    • Core Platform
    • Data Center
    • Data Science Lab
    • Data Pipeline
    • Data Visualization
      • Self-Service
      • Governed Dashboards
  • Core Platform
    • About Data Platform
      • Why Data Platform
      • Design Philosophy
      • Plugin Architecture
    • Getting Started
      • Sign in
        • Resetting Password
        • Force Login
      • Homepage
        • Data Catalog Search
        • AI Search
        • Apps Menu
        • Notification
        • Help Menu
        • User Profile
        • Search Option
        • Documents Folders
          • Document Options
            • Accessing Document Options
            • Options Assigned to a Folder
            • Options Assigned to a Linked URL
            • Options Assigned to a Story
            • Options for a Published Dashboard
        • Sorting Documents
        • Filter
      • Signing Out
    • Platform Administration
      • Accessing the Admin Module
      • Admin Panel Options
        • Document Management
        • Configurations
          • Geo Spatial
          • Data Science Servers
          • Bulk User Creation
          • Custom Field Settings
          • Data Connectors
          • API Connectors Configurations
          • Encryption
          • Form Settings
          • Data Sheet Settings
          • Data Lake Settings
          • Data Catalog Settings
          • Open ID Settings
          • Version Control
          • New Version Control
            • Versioning
            • Migration
          • Pipeline Settings
          • Keycloak Settings
          • Core Ownership Transfer
          • Email Server
          • Password
          • Sandbox Settings
          • Secret Management
          • DS Lab Settings
          • Data Store Settings
        • Authentication
          • AD Configuration
          • CA PPM Configuration
          • AWS Cognito Configuration
        • Audit Trail
          • Log Status
          • Audit Log Table
        • Language Mapping
          • Languages
          • Mapping Table
        • Migration
          • SFTP Settings
          • Document Migration
          • DSW Migration
        • GIT Migration
          • Migrating a Dashboard
          • Migrating an API Service
          • Migrating a Pipeline
        • Session Manager
        • Schedule Monitor
        • Server Monitor
        • License
        • API Client Registration
    • Users & Roles
      • User Security Page
      • Creating a new User
        • Restrict Data Access
      • Creating a new User Group
        • Assigning Custom Fields to Group Users
      • Various User Roles
        • Viewer Role
        • Admin Role
        • Non-admin User Roles
      • User Status
  • Data Center
    • Homepage
    • Data Virtualization
    • Data Connectors
      • Creating a Data Connector
      • Data Connector List
        • Edit Data Connectors
        • Create Option
        • Reconnecting to a Data Connector
        • Sharing a Data Connector
        • Delete a Data Connector
      • Supported Data Connectors
        • Database Connectors
          • MySQL
          • MSSQL
          • Elastic (Beta Release)
          • Oracle
          • ClickHouse
          • Arango DB
          • Hive
          • Cassandra
          • MongoDB
          • MongoDB for BI
          • PostgreSQL
          • Snowflake
        • File Data Connector
        • API Connectors
          • API Connector
          • Amazon
          • App Store
          • Bing Ads
          • Dropbox
          • FTP Server
          • Facebook
          • Facebook Ads
          • Firebase DB
          • Fitbit
          • Flipkart
          • Google Adwords
          • Google Analytics
          • Google Big Query
          • Google Forms
          • Google Sheet
          • HubSpot
          • Jira
          • Lead Squared
          • Linkedin
          • Linkedin Ads
          • MS Dynamics
          • Mailchimp
          • QuickBooks
          • SalesForce
          • ServiceNow
          • Twitter
          • Twitter Ads
          • Yelp
          • YouTube
          • ZOHO Books
        • Others
          • MS Sql Olap
          • Data Store
          • OData
          • Spark SQL
          • AWS Redshift
          • SAP HANA
    • Data Sets
      • Creating a New Data Set using RDBMS Connector
      • Creating a Data Set using Arango DB Connector
      • Creating a Data Set using an API Connector
      • Creating a New FTP Data Set
      • Creating a Data Set based on an Elastic Connector
      • Data set list page
        • View Options: Data Sets List Page
        • Data Set List: Actions
    • Data Stores
      • Creating a New Data Store
        • Data Store using an RDBMS Connector
        • Data Store using a Flat File Data Connector
        • Data Store using an API Data Connector
      • Adding Synonyms to a Datastore
      • Data Stores List
    • Data Store Meta Data
      • Sharing a Meta Data Store
      • Deleting a Meta Data Store
    • Data Sheets
      • Creating a Data Sheet
      • Publishing a Data Sheet
        • Entering Data
        • Viewing Data
        • Deleting a Row
      • Editing a Data Sheet
      • Removing a Data Sheet
    • Data Catalog
    • Data Sandbox
      • Creating a Data Sandbox File
      • Data Sandbox List Page
        • Uploading File Status
        • Using the Data Preparation Option
        • Deleting a Data Sandbox
    • Data as API
    • Data Preparation (Beta Release)
      • Accessing the Data Preparation Option
      • Data Preparation Workspace
        • Data Grid
          • Data Grid Header
          • Data Quality Bar in the Grid
        • Profile: Summary Pane
          • Charts
          • Info: Values/Statistics
          • Pattern
        • Transforms
          • Data Cleansing
          • String
          • Numbers
          • Columns
          • Conversions
          • Integer
          • Dates
          • ML
          • Anonymization
        • Steps
      • Data Preparation List
        • Rename
        • Edit
        • Delete
  • Data Science Lab
    • What is Data Science Lab?
      • Design Philosophy
      • What is a DSL Project?
    • Getting Started
      • Accessing the DS Lab Module
    • Start your Data Science Experiment with DS Lab
    • Project
      • Creating a Project
      • Keep Multiple Versions of a Project
      • Sharing a Project
      • Editing a Project
      • Activating a Project
      • Deactivating a Project
      • Deleting a Project
      • Various Tabs to work with
        • Notebook
          • Ways to Access Notebook
            • Creating a Notebook
            • Uploading a Notebook
          • Notebook Page
            • Notebook Cells
              • Using a Code Cell
              • Using a Markdown Cell
            • Modifying a Notebook
            • Notebook Task Bar
            • Notebook Operations
              • Datasets
              • Secrets
              • Algorithms
              • Transforms
              • Models
                • Registering a Model
                • Filtering a Model
              • Predict
              • Artifacts
                • Preview Artifact
              • Variable Explorer
              • Find and Replace
          • Notebook List Page
            • Export
              • Export to Pipeline
              • Export to GIT
            • Notebook Version Control
            • Sharing a Notebook
            • Editing a Notebook
            • Delete a Notebook
        • Dataset
          • Adding Data Sets
            • Data Sets
            • Data Sandbox
          • Dataset List Page
            • Preview
            • Data Profile
            • Create Experiment
            • Data Preparation
            • Delete
        • Utility
        • Model
          • Export to GIT
          • Register a Model
          • Unregister a Model
          • Register Model as an API Service
            • Register a Model as an API
            • Register an API Client
            • Pass Model values in Postman
        • Auto ML (Alpha Release)
          • Creating Experiments
            • Accessing the Create Experiment Option
              • Configure
              • Specify Prediction
          • AutoML List Page
            • View Report
              • Details
              • Models
                • View Explanation
                  • Model Summary
                  • Model Interpretation
                    • Individual Explanation
                    • Partial Dependence
                    • Model Performance
                    • Feature Importance
                  • Dataset Explainer
            • Delete
  • Data Pipeline
    • About Data Pipeline
      • Design Philosophy
      • Low Code Visual Authoring
      • Real-time and Batch Orchestration
      • Event based Process Orchestration
      • ML and Data Ops
      • Distributed Compute
      • Fault Tolerant and Auto-recovery
      • Extensibility via Custom Scripting
    • Getting Started
      • Homepage
        • List Pipeline
        • Creating Pipeline
          • Adding Components to Canvas
          • Connecting Components
          • Events [Kafka and DB Sync]
          • Memory and CPU allocations
        • List Components
        • Delete Orphan Pods
        • Scheduler
        • Data Channel
        • Settings
      • Pipeline Workflow Editor
        • Pipeline Toolbar
        • Component Panel
        • Right-side Panel
      • Testing Suite
      • Activating Pipeline
      • Monitoring Pipeline
    • Components
      • Adding component to worflow
      • Component Architecture
      • Component Base Configuration
      • Resource Configuration
      • Intelligent Scaling
      • Connection Validation
      • Readers
        • S3 Reader
        • HDFS Reader
        • DB Reader
        • Elastic Search Reader
        • SFTP Stream Reader
        • SFTP Reader
        • Mongo DB Reader
          • Docker Reader
          • Spark
        • Azure Blob Reader
        • Azure Metadata Reader
        • ClickHouse Reader [Docker]
      • Writers
        • S3 Writer
        • RDBMS Writer
        • HDFS Writer
        • ES Writer
        • Mongo Writers
          • Mongo Writer (Spark)
          • Mongo Writer(Docker)
        • Azure Writer
        • ClickHouse Writer [Docker]
        • Sandbox Writer
      • AI/ML
        • Python Model Runner
        • DSL Model & Script Runner
      • Consumers
        • SFTP Monitor
        • MQTT Consumer
        • Eventhub Subscriber
        • Twitter Scrapper
        • API Ingestion and Webhook Listener
        • Mongo Change Stream
        • Rabbit MQ Consumer
        • AWS SNS Monitor
        • Kafka Consumer
        • Kafka Producer
      • Producers
        • WebSocket Producer
        • Eventhub Publisher
        • EventGrid Producer
        • Rabbit MQ Producer
      • Transformation
        • SQL Component
        • Dataprep Script Runner
        • File Splitter
        • Rule Splitter
        • Stored Procedure Runner
        • Flatten JSON
        • Email Component
        • Pandas Query Component
        • Enrichment Component
        • Mongo Aggregation
        • Data Loss Protection
      • Scripting
        • Script Runner
        • Python Script
      • Scheduler
    • Custom Components
    • Advance Configuration & Monitoring
      • Configuration
        • Kafka Configuration
        • Default Component Configuration
        • Logger Setting
      • Data Channel
      • Cluster Events
      • System component Status
    • Version Control
    • Use Cases
  • Data Visualization
    • Self Service
      • Getting Started
        • What is Story?
        • Creating a new Story
          • Accessing the Story Module
            • Creating and Updating Instance
          • Designing a View
      • Design Workspace
        • Guided Tour
        • Dimension Profiling
        • Data Store Merge at View Level
        • Measure Summary
        • Series Properties
        • Formula Field Editor
          • Creating a Formula
            • Record Level Option
            • Summary Level Option
          • Creating a Range
        • Order by and Limit
        • Adding a Slicer
      • Chart Gallery
        • Mixed chart
        • Area chart
        • Bar chart
        • Bubble chart
        • Column Stack chart
        • Line chart
        • Pie chart
        • Scattered Plot chart
        • TreeMap chart
        • Circumplex chart
        • Pareto chart
        • Semi Gauge
        • KPI Tile
        • KPI Tile: Comparative Tile
        • KPI Tile: Sparkline
        • Map
        • Data Grid
        • Metric Summary
        • R Server Visual
        • Dissolution chart
        • Spider chart
        • Waterfall chart
      • Storyboard
        • Search
        • Shared Views
        • Export
        • Alert Center
        • Change Theme
        • Data Store Information
        • Options Assigned to a View
          • Analyse
            • Timeline Play
          • Edit
          • Remove
        • Data Interactions/ Data Drills
          • Drill Into
          • Drill Through
      • Applying Filters
        • View Specific Filter
          • Dimension-based View Filter
          • Measure-based View Filter
          • Date-based View Filter
        • Global Filter
          • Exclude from the Global Filter
          • Saving a Global Filter
          • Custom View Filter
          • Like and Not Like Filter Operations
      • Actions
        • Interactions
    • Governed Dashboards
      • About Dashboard Designer
        • What is Dashboard Designer?
        • Why is it used?
      • Getting Started
        • Accessing the Designer Module
        • Overview of Designer Module
          • Homepage
            • Guided Tour
            • Left Menu Panel
              • New
              • Manage
              • Open Dashboard
              • Preferences
              • Save As
              • Help
              • Exit
          • Dashboard Canvas Page
            • Right side Panel
              • Connection Page
              • Chart Container
              • Manage Window
              • Script Window
              • Guided Tour
            • Canvas Properties
            • Context Menu Properties
      • Create New Workspace
        • Workspace Creation: Complete Flow
        • Creating a Workspace
        • Renaming a Workspace
        • Deleting a Workspace
      • Create New Dashboard
        • Dashboard Creation: Complete Flow
        • Adding a New Dashboard
        • Create Connection
        • Drag and Drop Charts
        • Associate the Dataset
        • Preview the Dashboard
        • Save the Dashboard
      • Managing Options for a Dashboard
        • Open Dashboard in Designer
        • Publish a Dashboard
        • Share a Dashboard
        • Dashboard Version Control
        • Action Menu
          • Preview Dashboard
          • Export to Local Disk
          • Rename
          • Delete
          • Moving a Dashboard
          • Information Icon
      • Connecting to a Data Source
        • Accessing the Data Connectors
        • Establishing a Data Connection
          • CSV Connection
          • Excel Connection
          • Data Service Connection
          • Data Science Service
          • Data store Connection
          • Data Sheet
            • Data Sheet Enhancements
          • WebSocket Connection
          • Merged Connection
      • Charts Gallery
        • Charts
          • Area Chart
          • Bar Chart
          • Bubble Chart
          • Circumplex Chart
          • Column Chart
          • Funnel Chart
          • Histogram Chart
          • Inverted Funnel
          • KPI Tile
          • Line Chart
          • Map Chart
            • Leaflet Properties
          • Mito Plot
          • Mixed Chart
          • Pie Chart
          • Project Timeline
          • Pyramid Chart
          • Spark Line
          • Scatter Plot
          • Spider Chart
          • Waterfall Chart
        • Grids
          • Data Grid
          • Paging Grid
          • Data Sheet
          • Scorecard
          • Pivot Grid
        • Filters
          • Checkbox
          • Combobox
          • Hierarchical Combobox
          • List
          • Radio Button
        • Advanced Charts
          • Box Plot
          • Candle Stick
          • Chevron
          • Data Search
          • Decision Tree
          • Group Bar
          • Group Column
          • Heat Map
          • Text Analyzer
          • Time Series
          • Tree Map
          • Trellis
          • Word Cloud
        • Other Charts
          • Box
          • Bullet
          • Date Picker
          • Export
          • Filter Chips
          • Filter Saver
          • Gauge
          • Graphics
          • Guided Tour
          • H-Slider
          • Image
          • Info Button
          • Label
          • Legend
          • Progress Pie
          • Semi Gauge
          • Stepper
          • SVG Image
          • Text Box
          • Trend
          • Url Button
          • V-Slider
        • Custom Charts
        • Common Chart Properties
          • Background
          • Title & Sub-title
          • X & Y Axis Properties
          • Legend Properties
          • Formatter
          • Axis Setup
          • Export Options
      • Dashboard Objects
        • Manage Dashboard Components
        • Dashboard Objects Properties
      • Configuration
        • Filtering the Data
        • Drill Through
        • Dataset Properties
        • Indicators in Charts
        • Tooltip(Default & Custom)
        • Data Label
        • Geo Mapping
        • Language Mapping
        • Legend Mapping
        • Alerts in Grids
      • Script Help Section
        • Navigate to Script Help page
        • How to use Scripts?
  • Survey
    • Accessing Survey Module
    • Creating a Survey
      • Creating a New Survey
      • Creating a New Survey using Template
    • Survey Builder: Designing a Survey
      • Questions
        • Inserting a Question
        • Available Question Types
        • Deleting a Question
      • Page
        • Inserting a New Page
        • Editing an Existing Page
      • Survey Options
      • Properties
      • Theme
      • Saving a Survey
    • Publishing a Survey
      • Providing the Publish Survey Information
      • Collectors
        • Accessing the Collector Options
        • Creating a Collector
          • Creating a Weblink Collector
          • Creating an Email Collector
            • Adding Recipient to an Email Collector
        • Editing Collector(s)
        • Deleting Collector(s)
    • Analyzing Result for a Survey
      • Creating a New View in the Analyze Result
      • Filter Rule in the Analyze Result
        • Creating a Filter
        • Filter Types
      • Show/Hide Rules for Page/Questions
    • More Options
      • Preview Survey
      • Benchmark
        • Editing a Benchmark
        • Benchmark Goals
          • Adding a Goal to Benchmark
          • Editing a Benchmark Goal
          • Deleting a Benchmark Goal
        • Questions for Benchmark Goals
          • Adding Questions to a Benchmark Goal
          • Viewing a Goal Question
          • Deleting a Goal Question
      • Managing Datamart
        • Creating a Datamart
        • Implementing Scheduler for Datamart
        • Other Options
      • Copying a Survey
      • Survey Summary
      • Deleting a Survey
    • Contacts
      • Creating a Contact Group
        • Editing a Contact Group
        • Deleting a Contact Group
      • Creating New Contacts
        • Creating New Contacts (Manually)
        • Uploading Contacts from a CSV file
      • Listing a Contact
    • Survey Template
  • Forms
    • Accessing the Forms Module
    • General Workflow for Forms
      • Creating a Form
      • Listing a form
      • Form Page Component
      • Page Settings
        • Rename
        • Duplicate
        • Delete
      • Question
        • Duplicate
        • Delete
        • Properties
          • Properties
          • Condition
          • Look up
      • Form Preview
      • Publishing a form
        • Options Context Menu
          • Open in New Tab
          • Properties
          • Modifying a form
          • Adding a form to favorite
          • Moving a form
          • Renaming a form
          • Coping a form
          • Deleting a form
      • Form Response
        • Filter Response
          • Filtering Responses by Page
          • Filtering Responses by Date
          • Filtering Responses by Users
      • Form Settings
        • Properties
          • Status
          • Configuration
        • Theme
          • Form Header Theme
          • Page Header Theme
          • Form Body Theme
        • Form Instruction per User
    • Form Options
      • Edit
      • Delete
      • Duplicate
Powered by GitBook
On this page
  • Concatenate with
  • Delete Column
  • Return Non-Null Column Values
  • Collect Set
  • Cast to Types
  • Duplicate Columns
  • Generate Primary Key
  • Rename Column
  • Get JSON Objects
  • Fill Empty
  • Get Character Length
  • Pivot

Was this helpful?

  1. Data Center
  2. Data Preparation (Beta Release)
  3. Data Preparation Workspace
  4. Transforms

Columns

PreviousNumbersNextConversions

Last updated 2 years ago

Was this helpful?

Concatenate with

The users can concatenate a column value with some other column or with some prefix/suffix.

To perform the transform, select the column to which data must be concatenated and select the Concatenate with transform. The available options are:

  • Prefix: Specify the value to be prefixed to the selected column value

  • Use with:

    • Select the Value to add a Prefix/Suffix

    • Select the Other column to concatenate two columns

  • Suffix: Specify the value to be suffixed to the selected column value returns when performed on the selected column.

The below configuration provides ‘BDB’ as the prefix for the new column, id_concatanate_1.

Please Note: The users must select the Use with Other Column option to concatenate a value with another column and select the Use with Value option to add prefix/suffix.

Delete Column

It deletes any selected column.

To perform the transform, select the Column category using transform tab and click on the Delete Column transform.

or

Return Non-Null Column Values

The transform returns the first non-null value from the list of columns specified to a new column.

To perform the transform, select the columns which must be checked for null and specify a column name for the result.

  • Select Column: Select the columns to be checked for null

  • Column name: The name for the new result column returns

The Return Non Null Column Values transform has been applied to the monthly_salary and cur_monthly_payment columns. As a result, the Payment column gets added to the dataset with the values based on the applied transform.

Collect Set

The Collect Set transform generates the list of all the unique values of the column based on the selected column. It performs group concatenation.

  • Configure the Transform and click the Submit option. The selected Column is source.

  • It generates a list of all unique values under the source_collect_set_1 as displayed in the below image:

Cast to Types

It is a table-based operation. The profiling of a column is done based on the data type present in the majority. Let’s say in column A; we have four integer values and one string value, then the data type of column gets profiled as the integer despite one string value present in it. The ‘Cast to Types’ transform removes the value with the invalid data type. In this case, it converts data with a string data type to the null value.

Please Note: Cast to types is a lossy transformation. There is a possibility of some data loss.

Duplicate Columns

The Duplicate Columns transform creates another column containing the duplicate data of the selected column.

It inserts a duplicate column of the selected column in the dataset.

Generate Primary Key

It generates the primary key for the table. It is a table-based operation.

  • Use with: The user gets two options to generate the primary key:

    • Contiguous- it generates the auto-incremented value starting from 1.

    • Non_contiguous- it generates a unique and random integer value.

  • A new column with primary values gets added to the data grid.

Rename Column

The Rename Column transform allows the user to rename the selected column.

  • Select a column from the data grid that needs to be renamed.

  • Open the Transforms tab.

  • Choose the Rename Column transform.

Or

  • Select a column from the data grid that needs to be renamed

  • Click the Menu icon provided next to the Column name.

  • Select the Rename Column option from the context menu.

  • The Rename Column dialog box opens (In both the scenarios).

  • Provide a name that you wish to use as a rename for the selected column.

  • Click the Submit option.

  • The column gets renamed.

Get JSON Objects

The Get JSON Objects transform extracts any parameter from a given column with JSON data. When the parameter in the JSON is specified, the transform extracts all parameter values as columns into a tabular format.

  • To extract the nested Json the ‘.’ mark can be used to specify the path.

  • The above data can be extracted using the transform by specifying the parameters as given below:

  • The data is converted to a tabular structure as below:

Fill Empty

The Fill Empty transform is used to fill the null/empty value of cell using either above or below values available in the column.

  • Configure the Fill Empty transform:

    • Create new column- Click the checkbox to create a new column or else the currently selected column gets updated.

    • Use with-The user can use either of the options from the provided choices:

      • From Above: To fill the empty cells and replace them by the value of the cells given above the empty cells.

      • From Below: To fill the empty cells and replace them by the value of the cells given below the empty cells.

    The Fill Empty transform has been applied to the Gender column by filling the empty cells with the values of the immediate below cells.

As a result, a new column gets created with some of the empty cells filled by the values of the immediate below given cells as shown in the below image:

Please Note: The Fill Empty transform fills the immediate empty cell with either above or below cell's values. It does not fill the cells for which both above and below cells are empty.

Get Character Length

The transform Get Character Length when applied adds a new column with numbers displaying the length of character present in that cell.

The Get Character Length transform has been applied to the designation column.

As a result, a new column gets added next to the designation column displaying the character length of each cell value as displayed below:

Please Note:

  • This transform counts the space provided between two words as a character. So, to get the exact count of the character length the cell should not have any space between two words.

  • The empty cells are kept as it is in the column.

Pivot

When applied, the Pivot transform converts the data into a Pivot table based on the selected Pivot Column and Group of the selected columns.

Sample Data:

Transform Selection: In the given sample data, the Quarter is selected as the Pivot Column, and Sum aggregation is selected for the Sales column. The selected group columns are Category and Product.

As a result, it returns the sum of Sales for each Quarter based on the aggregation of Category and Product columns.

The user can click on the Menu icon for a column, it will display the Delete column option. By clicking the Delete column option also the selected column gets removed from the dataset.