• Blog
  • Contact
  • Sign in
CloverDX
Product
  • Overview
  • CloverDX Data Integration Platform
  • What's new in CloverDX 6
  • Pricing
  • CloverDX plans
  • Deployment
  • CloverDX on AWS
  • CloverDX on Azure
  • CloverDX on Google Cloud
  • CloverDX on-premise
  • Resources
  • Customer Portal
  • Documentation
  • Downloads & Licenses
  • Webinars
  • Academy & Training
  • Release Notes
  • CloverDX Forum
  • CloverDX Blog
  • Tech Blog
  • Other resources
isometric-illustration--product@2x 1

Get under the hood of CloverDX

See how CloverDX can benefit your business with a live demo. Simply get in touch with our team and we’ll handle the rest.

Book a demo
Solutions
  • By Industry
  • Banking
  • Capital Markets
  • Consultancy & Advisory
  • FinTech
  • Government Agencies
  • Healthcare
  • By Use Case
  • Data Quality
  • Data Ingest
  • Data Warehousing
  • Data Migration
  • Digital Transformation
  • Enterprise Data Management
  • Risk & Compliance
  • Anonymization
How F3 Group use CloverDX to ingest more client data - webinar
Customer interview

Formula 3: Staying Small And Agile While Working With Large Enterprise Ecosystems

Browse webinars
Services
  • Services
  • Onboarding & Training
  • Professional Services
  • Customer Support

More efficient, streamlined data feeds

Discover how Gain Theory automated their data ingestion and improved collaboration, productivity and time-to-delivery thanks to CloverDX.

 

Read case study
Customers
  • By Use Case
  • Analytics and BI
  • Data Ingest
  • Data Integration
  • Data Migration
  • Data Quality
  • Data Warehousing
  • Digital Transformation
  • By Industry
  • App & Platform Providers
  • Banking
  • Capital Markets
  • Consultancy & Advisory
  • E-Commerce
  • FinTech
  • Government
  • Healthcare
  • Logistics
  • Manufacturing
  • Retail
Migrating data to Workday - case study
Case study

Effectively Migrating Legacy Data Into Workday

Read customer story
Company
  • About CloverDX
  • Our story & leadership
  • Contact us
  • Partners
  • CloverDX Partners
  • Become a partner
Pricing
Demo
Trial

Iteration through the Record Fields in CTL

Data Integration Data Transformation CloverDX How-To
Posted January 25, 2010
2 min read
Iteration through the Record Fields in CTL

Note: this post relates to CloverDX 2.x version with CTL version 1. Starting with CloverDX 3.x , CTL version 2 was introduced which has a bit different syntax. However you can still use CTL 1 in transformations - see CTL1 vs CTL2 comparison.

Recently, I have been facing a very common problem. Imagine this scenario: I have two files – the first one with origin records and the second one with slightly modified new records. Each record had a unique key and approximately 50 fields. My task was to compare these two files and find out how many fields in every pair differ from each other in the corresponding records.

The simplified graph can be seen in following picture:

data_intersection-1

 

A comparison of two records can be processed by the CloverDX DataIntersection component which joins the records with the same keys. In the joined records, you can compare fields that are not part of the key. But remember, you have to write the comparison in the CTL transformation ;-) . Of course you could write the following block of code for each pair of compared fields:

int count = 0;
if(nvl($0.field_N, '') != nvl($1.field_N, '')) {
count++;
//but typically more actions :-(
}
//and imagine this block 50times :-(
//final mapping
$0.key := $0.key;
$0.count := count;

But this solution takes too much time when you have to repeat it for many fields (approx. 50 in my case). It is also very slow, uncomfortable and increases the probability of making a mistake in your code (e.g. omitting some fields). Fortunately, CloverDX allows you to iterate through the fields of processed records! :-) The code is then more briefer and more generic:

//declaration of variables for copies of input records
record(Metadata1) myrec1;
record(Metadata1) myrec2;
function transform() {
int i = 0;
int count = 0;
//asign value of input records to local variables
myrec1 = @0; //myrec1 is a copy of a current record on input port 0
myrec2 = @1; //myrec2 is a copy of a current record on input port 1
//iterate through fields, suppose that field with index 0 is the key
for(i = 1; i < length(myrec1); i++) {
if(nvl(myrec1[i], '') != nvl(myrec2[i], '')) {
count++;
}
}
//final mapping
$0.key := $0.key;
$0.count := count;
}

Someone could object to the necessity of making copies of records. In this case I have good news. CTL in CloverDX version 2.9 introduces the possibility of iterating directly through the fields of input records.

@0[i];//i-th field of the input record on port 0

Moreover new functions for getting field names and data types are introduced in version 2.9. Personally, I am looking forward to such features that will make CTL code simplier and clearer.

Share

Facebook icon Twitter icon LinkedIn icon Email icon
Try CloverDX for 45 days  Full access to Tech Support as if you were a customer

Newsletter

Subscribe

Join 54,000+ data-minded IT professionals. Get regular updates from the CloverDX blog. No spam. Unsubscribe anytime.

Related articles

Back to all articles
Steps to providing a data-driven customer experience
Data Integration
5 min read

4 steps to providing a data-driven customer experience

Continue reading
Dissecting complex data transformations: 3 real-world examples
Data Transformation
4 min read

Dissecting complex data transformations: 3 real-world examples

Continue reading
Replacing scripting with modern data integration
Data Integration
3 min read

Replacing scripting with modern data integration

Continue reading
CloverDX logo
Book a demo
Get the free trial
  • Company
  • Our story
  • Contact
  • Partners
  • Our partners
  • Become a partner
  • Product
  • Platform overview
  • Plans & Pricing
  • Customers
  • By Use Case
  • By Industry
  • Deployment
  • On-premise
  • AWS
  • Azure
  • Google Cloud
  • Services
  • Onboarding & Training
  • Professional Services
  • CloverCARE Support
  • Resources
  • Customer Portal
  • Documentation
  • Downloads & Licenses
  • Webinars
  • Academy & Training
  • Release Notes
  • CloverDX Forum
  • CloverDX Blog
  • Tech Blog
  • Other resources
Blog
4 steps to providing a data-driven customer experience
Data Integration
Implementing data democratization: 3 ways to make your data more accessible
Data Innovation
Data dictionary vs data catalog: what’s the difference?
Data Innovation
What is a ‘live’ data catalog and how can you use one in your organization?
Data Innovation
© 2023 CloverDX. All rights reserved.
  • info@cloverdx.com
  • sales@cloverdx.com
  • ●
  • Legal
  • Privacy Policy
  • Cookie Policy
  • EULA
  • Support Policy