Overview
Data wrangling operations are used to transform data between JSON, delimiter-separated values, tuples, arrays, and base64-encoded file packages. These operations help structure data so it can be stored, transmitted, grouped, summarized, or reformatted as required.
Single Line to Multiline
Single Line to Multiline converts Single Line JSON into Multiline JSON. The resulting JSON string can be used to store or transmit data in a structured format.
Number of Parameters: 1
Overview
Single Line to Multiline is used when a single-line JSON structure needs to be converted into multiline JSON format.
Parameter : Chopkey
Chopkey adds a new key on the fly with its value as a static value or dynamic value.
How to Configure / How to Use
Use the Chopkey parameter to define the key path where the new key is added.
['bizdata_dataset_response']['data']
Delimiter to JSON
Delimiter to JSON converts delimiter data into JSON data. The resulting JSON string can be used to store or transmit data in a structured format.
Number of Parameters: 6
Overview
This operation is used when delimiter-separated input must be transformed into structured JSON.
When to Use
- When source data is separated by delimiters such as commas.
- When structured JSON output is required for storage or transmission.
- When column names must be auto-generated or explicitly defined.
Parameter : Key Data
Key Data holds the value of delimiter data that must be converted into JSON data.
['DataTable']
Parameter : Delimiter
Delimiter is used to separate values in a list, record, or file.
,
Parameter : Fields
Fields is used to specify header names. It is not mandatory. If fields are not provided, predefined fields will be generated.
"Customer ID","Organization Name","Month","Item"
Parameter : Autodetect Column Names
Autodetect Column Names will be false if the user defines fields. Otherwise, it will be true.
true
Parameter : Skip Header
If the user defines the fields, Skip Header will be true. Otherwise, it will be false.
false
Parameter : Response Key
Response Key is the key in which the delimiter data will be stored.
datatable
How It Works
- Reads delimiter-separated input from the specified key.
- Uses the provided delimiter to split values.
- Applies either user-defined fields or predefined column names.
- Stores the converted JSON in the configured response key.
JSON to Delimiter
JSON to Delimiter converts JSON data into delimiter data. The resulting delimiter data can be used to store or transmit data in a structured format.
Number of Parameters: 6
Overview
This operation is used when structured JSON data must be converted into delimiter-separated output.
Parameter : Key Data
Key Data holds the value of JSON data that needs to be converted into delimiter data.
['items']
Parameter : Delimiter
Delimiter is used to separate values in a list, record, or file.
\t
Parameter : Fields
Fields is used to specify header names. It is not mandatory. If fields are not provided, predefined fields will be generated.
"Item1","Item2","Item3"
Parameter : Autodetect Column Names
Autodetect Column Names will be false if the user defines fields. Otherwise, it will be true.
true
Parameter : Skip Header
If the user defines the fields, Skip Header will be true. Otherwise, it will be false.
false
Parameter : Response Key
Response Key is the key in which delimiter data will be stored.
delimiter_data
How It Works
- Reads JSON data from the specified key.
- Extracts values using the selected delimiter format.
- Applies optional field names if they are provided.
- Stores the generated delimiter output in the response key.
Data Aggregation
Data aggregation is used for the processing of raw data. It also helps in grouping, summarizing, and processing data to make it easier to understand and analyze.
Number of Parameters: 3
Overview
This operation groups related records and organizes them into a more usable structure.
When to Use
- When related records must be grouped by a unique identifier.
- When common keys must be collected under a shared array.
- When raw data needs to be summarized into a grouped format.
Parameter : Agg Data Key
Agg Data Key is passed as empty for multiline data and will have a data key in case of single line data.
Use this parameter when you have multi-line JSON data, otherwise leave it blank.
['bizdata_dataset_response']['items']
Parameter : Groupby Key
Groupby Key gives the key name that the user wants to group by. This is a unique identifier in the dataset.
"Orders"
Parameter : Array Key
Array Key gives the key where the user wants to hold the common keys. The user can provide any key name.
"Order Lines"
Parameter : Array Key Nested Columns
Array Key Nested Columns defines the comma-separated keys that should be available inside the Array Key.
"id","name","year"
How It Works
- Reads source data from the aggregation key.
- Groups records using the Groupby Key.
- Stores related values under the configured Array Key.
- Uses nested columns to define the values available inside the grouped array.
Unpivot
Unpivot converts a single object into a list of objects based on transposed values that track transposed key name parameters.
Number of Parameters: 2
Overview
Unpivot is used when selected fields in a single object need to be converted into a list-based structure.
Parameter : Transposed Key Name
Transposed Key Name specifies the name of the key that needs to be transposed.
"bucket_type","bucket_value"
Parameter : Transposed Value
Transposed Value specifies the values that need to be transposed.
"on_hand", "purchase_orders","goods_in_transit"
How It Works
- Uses transposed key names to identify the keys involved in the transformation.
- Uses transposed values to determine which values are converted.
- Creates a list of objects from a single object.
Pivot
Pivot combines multiple dictionaries (objects) into a single dictionary (object) based on the transposed value and the transposed key name provided by the user.
Number of Parameters: 3
Overview
Pivot is used to promote selected values from a nested structure into root-level keys.
Parameter : Get Key
Get Key is passed as empty for multiline data and will have get key in case of single line data.
items
Parameter : Transposed Key Name
Transposed Key Name specifies the name of the key that needs to be transposed.
"Item Id","Item Name"
Parameter : Transposed Value
Transposed Value specifies which values need to be transposed.
"OrderID-1", "OrderID-2"
How It Works
Pivot keeps the original attributes array unchanged while transposing selected key-value pairs to the root level of the object.
Example Scenario
Consider the following input data representing a dataset with various attributes.
{"bizdata_dataset":{
"id":123,
"name":"sample",
"lastname":"dataset",
"attributes":[
{
"attributename":"item",
"attributevalue":"27",
"attribute_code":12234
},
{
"attributename":"item2",
"attributevalue":"47",
"attribute_code":12334
},
{
"attributename":"item1",
"attributevalue":"37",
"attribute_code":13234
}]}}
Pivot Operation Parameters
Parameter : Get Key
attributes
Parameter : Transposed Key Name
"attributename"
Parameter : Transposed Value
"attributevalue"
Sample Output
After applying the Pivot Operation, the input data will be transformed as follows:
{"bizdata_dataset":{
"id": 123,
"name": "sample",
"lastname": "dataset",
"attributes":[
{
"attributename": "item",
"attributevalue": "27",
"attribute_code": 12234
},
{
"attributename": "item2",
"attributevalue": "47",
"attribute_code": 12334
},
{
"attributename": "item1",
"attributevalue": "37",
"attribute_code": 13234
}],
"item": "27",
"item2": "47",
"item1": "37"}}
Explanation of Output
- The original array of attributes remains unchanged.
- The attributename values (“item”, “item2”, “item1”) are transposed to the root level of the bizdata_dataset object as keys.
- The corresponding attributevalue values (“27”, “47”, “37”) are associated with the newly created keys (“item”, “item2”, “item1”).
Single Line to Tuple
Single Line to Tuple converts a single line of data to a tuple.
Number of Parameters: 3
Overview
This operation is used when a single-line dataset must be converted into tuple format.
Parameter : Singleline Key
Singleline Key helps in reading the dataset from a single line.
['DataTable']
Parameter : Table Headers
Table Headers specifies the sequence of the converted tuple data.
"Item","Customer","Month"
Parameter: Tuple Key
Tuple Key is the key that holds tuple data.
data
Tuple to Single line
Tuple to Single line converts a tuple into a single line string.
Number of Parameters: 3
Overview
This operation is used when tuple data must be converted into single-line data.
Parameter: Tuple Key
Tuple Key is used to read the user’s tuple data.
['Data']
Parameter: Headers
Table Headers are the headers or key names of the new JSON. The values of these key names will appear in the same sequence in the singleline data.
"Item","Customer","Month"
Parameter : Singleline Key
Singleline Key helps in storing the converted singleline data.
datatable
Delimiter to Array
Delimiter to Array converts a given key’s delimited value into an array.
Number of Parameters: 2
Overview
This operation is used when delimiter-separated values must be converted into an array.
Parameter: Dl Key
Dl Key converts a given key’s delimited value into an array.
Below is the example how we can use Dl key.
Parameter: Delimiter
Delimiter gives the delimiter used to separate the delimited values. Delimiter can be any of ,, /t, | etc.
/t
How It Works
Input –
EMAIL : john.doe@email.com
Output –
: john
: doe@email
: com.
In this example, Delimiter is dot(.) and Dl key is EMAIL. This operation converts delimiter-separated values to an array.
Frequently Asked Questions
What is the purpose of Delimiter to JSON?
It converts delimiter-separated text into structured JSON format.
When should Data Aggregation be used?
It should be used when grouping and summarizing related records.
What does Pivot operation do?
It promotes selected attributes to root-level keys in the dataset.
Can Text files be converted into arrays?
Yes, using the Delimiter to Array operation.
Why use Zipfile in Base64?
It enables secure packaging and transmission of multiple files.
Notes
- Verify parameter formatting before execution.
- Test transformations using sample datasets.
- Apply operations sequentially for consistent results.
