Robust Transform - one resource row to multi rows in a table

[YonkH]

Hello

There is a row in staging table which contain a comma separated string, for example: "ip1, ip2, ip3", need insert to target IP Address table after split string by comma, will insert 3 rows. I think it need split the string to an array field first, then configure entity mapping, I have a try but inset failed, there is no detail description about this in SNOW docs, do you know whether Robust Transform support it and how to set?

Thanks

[Yashsvi]

Hi @YonkH, 

Yes, ServiceNow's Robust Transform Engine (RTE) can be used to split a comma-separated string and insert multiple rows into a target table. While detailed documentation for this specific use case might not be readily available, you can achieve this using a scripted transform map. Here’s a general approach:

Steps to Split a Comma-Separated String and Insert into a Target Table

1. Create a Transform Map:

   • Navigate to System Import Sets > Create Transform Map.
   • Create a new transform map that links your staging table to the target table.

2. Scripted Transform Map:

   • In the transform map, create a script to handle the splitting of the comma-separated string and the insertion into the target table.

3. Transform Script Example:

• Within the transform map, you can add a transform script on the relevant field. Here is an example of how to do this in a transform map script:

(function runTransformScript(source, map, log, target) {

    // Assuming 'source.field_name' contains the comma-separated string

    var ipAddresses = source.field_name.split(',');

    // Loop through each IP address and insert into the target table

    ipAddresses.forEach(function(ip) {

        // Trim whitespace from each IP address

        var trimmedIp = ip.trim();

        // Create a new record in the target table

        var newRecord = new GlideRecord('target_table_name');

        newRecord.initialize();

        newRecord.ip_address_field = trimmedIp; // Set the IP address field

        newRecord.insert();

    });

})(source, map, log, target);

By following these steps, you should be able to split a comma-separated string from a staging table and insert the values as separate rows in the target table using ServiceNow Robust Transform Engine and scripted transform maps.

Thank you, please make helpful if you accept the solution.

[YonkH]

Thanks for your reply, but not suitable for us. Because we already use RTE entity mapping, fields mapping and Entity Operations for other fields which include some parse process, we cannot rewrite all logic for other field in script, we want to use entity mapping way to insert rows to table. Do you have any idea here?

Thanks

[millerjosem]

In data processing, transforming one resource row into multiple rows in a table involves robust handling of data expansion. spectrum tv and internet packages for seniors This method is crucial for scenarios like splitting comma-separated values or duplicating rows based on specified conditions. Utilizing scripting or database operations, each transformed row enhances data granularity, facilitating detailed analysis and efficient data management. This approach ensures comprehensive utilization of resources while maintaining data integrity and clarity.

[KevinBellardine]

Hey @YonkH,

On your target entity definition you can create a new entity field that doesn't actually map to anything on the target table. You'll create a mapping to that 'field', and on the entity create a scripted entity operation that takes that field as an input and creates the records.

You're right that the Robust ETL can handle array mapping, but that can get extremely complicated and sounds like overkill for what you need. The documentation is poor, and I just tried to find it and failed, but essentially you tag an input as an array in your "import" entity by putting a [*] in the path name. Then, any entity you map to will also need to have that symbol in it's path name. It can get extremely complicated, particularly if you're dealing with multiple arrays with similar data as is often the case with IP related (for instance, an array of IP objects might have a version, IPv4 and IPv6. You'd need to create a separate array for each in order to handle them differently because of the way the data is loaded into the temporary entity).

The short story here is go with the entity operation. The array feature is overkill and there's a good chance you'd break the RTE trying to convert it.