Manipulating Data on Transform or Load

Go to solution
Jcharlie
Kilo Contributor

‎05-03-2022 02:39 AM

Hi All, 

I currently have a manual process that exists outside of ServiceNow. Data is extraced from a thrid party it is manipulated using excel macros into an acceptable format and then imported into ServiceNow using import sets.

I have been asked to investigate if the Data Manipulation is possible from within ServiceNow and so far I have not found a solution. Below I will share what the raw data looks like and how we need it to be imported into the table. 

Here is an example of the RAW data. This is to be imported in the departments table (cmn_departments) in this file each level of organisation (Org ID) represents a record. So for example

  • Org at level 1 has no parent org and is a record in the departments table
  • Org at level 2 has a parent that is the level 1 org 
  • Org at level 3 has a parent that is the level 2 org

And so on...

Raw Data examplefind_real_file.png

Here is how the data looks when it has been transformed outside of ServiceNow and is ready for import 

Sorted Data example
find_real_file.png

I have tried to acheive this with transform scripts but I havent found a way to split the records on the column. I have attempted manipulating the data on the import set table before running the transform but it doesn't 'feel right'.

Does anyone know of a way to achieve this in ServiceNow? For context these import have ~ 20,000 rows and are updated weekly.

Thanks 

Preview file
12 KB
Preview file
12 KB
Preview file
8 KB
  • 1,441 Views
1 ACCEPTED SOLUTION

Go to solution
Jorn van Beek
Tera Guru

‎05-03-2022 05:53 AM

Main question is why you need to do data manipulation other than transform (note transform is a data manipulation).

Your requirement is to load the original file to the department table in ServiceNow. Correct?

Well that is possible. Here are 2 options.

Highly scripted. Should think of this option if max layers is variable

  1. Create an import table for this file. that is including lvl1, lvl2, lvl3 etc
  2. Create a fully scripted transfrom map handling all layers during transforming.

No-code. Solution to run without coding.

  1. Create an import table for this file. that is including lvl1, lvl2, lvl3 etc
  2. Create an transform map for each layer in the organitation. Ensuring no record created on empty coalesc. So transform map 1 would have coalesc on org1 id, uses name of company 1 and department head of 1. transform map 2 would have coalesc on org2 id, uses name of company 2, department head of 2 and parent is org1. etc.
  3. ensure they are all run in a full tranform.

View solution in original post

6 REPLIES 6

Go to solution
Jcharlie
Kilo Contributor
In response to Jorn van Beek

‎05-08-2022 08:52 AM

Thanks for this reply, when you say "Create a fully scripted transfrom map handling all layers during transforming" How would you script this in a single transform map, I thought each row was processed in order and could only map to a single I'd field. So orgs ids after level 1 would not get added..
0 Helpfuls

Go to solution
Community Alums
Not applicable
In response to Jcharlie

‎04-03-2023 01:55 AM - edited ‎04-03-2023 01:56 AM

Hi JCharlie,

 

I just saw this, and wondered if you'd sorted it out? To keep it simple, you could run two transform maps against the data, in order:

1. 'Load IDs Transform Map' - coalesce on the ID field and load all of the IDs (and companies and department heads - ignore the parent field)

2. 'Load Parents Transform Map' - coalesce on the ID field again, but this time map the parent field, assuming that all of the IDs now exist

 

It's probably a bit late, now, but hope this helps.

 

Jason

0 Helpfuls