CTL - Add to lookup table?

CloverDX Platform
1

I’m working on a graph that is responsible for creating unique email addresses for a set of users. The email address is created as follows…

.@mycompany.com

…but a sequence number needs to be appended if two users end up with the same email address. For example, 3 people named John Smith end up with…

john.smith@mycompany.com
john.smith2@mycompany.com
john.smith3@mycompany.com

…I started by creating a DBLookupTable that selects all of the existing email address from the email address table. Then, after I generate the email address, I can check it against the lookup table to see if john.smith@mycompany.com already exists. If it does, I append a sequence number and check again. I keep checking until I find a sequence number that makes the email address unique.

The problem is, what if there are two John Smiths in the batch that I’m processing? I need to have a second lookup table, containing all of the email addresses created during the current run, check against both lookup tables.

Is it possible to write to a lookup table using CTL? Is there another way to do what I’m trying to do?

Thanks…

2

Hello Bernie,
putting data into lookup table from CTL is not possible. More over, lookup is performed not directly on lookup table, but on the lookup object created from lookup table. So even from java it wouldn’t work as you expect.
What about sorting the users before preforming lookup? Then you know that users with the same possible e-mails are grouped together.

3

I’m not sure how sorting would help. Here’s the situation…

Database


Firstname   Lastname   Email
John          Smith        js1@mycompany dot com
Jane          Smith        js2@mycompany dot com

New User List


Firstname   Lastname
Jerry         Smith
Jacob        Smith

So I start by creating a lookup table containing all of the existing email addresses in the database.

I process the first incoming record and choose the email address: js1@mycompany dot com
Check the lookup table, js1@mycompany dot com already exists, so I increment the sequence number: js2@mycompany dot com
Check the lookup table again, js2@mycompany dot com already exists, so I increment the sequence number: js3@mycompany dot com
Check the lookup table again, js3@mycompany dot com doesn’t exist, so that’s the email address I’ll insert…

Now I process the second incoming record and choose the email address: js1@mycompany dot com
Check the lookup table, js1@mycompany dot com already exists, so I increment the sequence number: js2@mycompany dot com
Check the lookup table again, js2@mycompany dot com already exists, so I increment the sequence number: js3@mycompany dot com

Now how do I know that js3@mycompany dot com doesn’t exist? It’s not in the database lookup table because it’s not in the database yet.

What I wanted to do is to have an empty lookup table that I can add email addresses that I choose, so I can check that table in addition to the database lookup table to make sure that the email address I choose is unique.

Right now I have this implemented in Java, by just using a class variable Set as a place where I can store the chosen email addresses as I go through the incoming records. I was hoping to stick to CTL though and not have to maintain Java code.

Thanks,
Bernie

4

Hello Bernie,
oh, I see. You can use list type and .in. operator:

//#TL
string key = "";
int counter;
string email;
list newEmails;

// Transforms input record into output record.
function transform() {
	counter = 2;
	key = concat($first_name, '.', $last_name);
	email = concat(key, "@mycompany.com");
	while (!isnull(lookup(LookupTable0,email).email)) {
		email = concat(key, counter++, "@mycompany.com");
	}
	while (email.in.newEmails) {
		email = concat(key, counter++, "@mycompany.com");
	}
	push(newEmails,email);
	$0.* := $0.*;
	$email := email;
	return ALL
}

This is my previous (with sorted records) solution:

//#TL
string key = "";
string tmpKey;
int counter;
string email;

// Transforms input record into output record.
function transform() {
	tmpKey = concat($first_name, '.', $last_name);
	if (tmpKey != key) {//first name and last name different than in previous record
		key = tmpKey;
		counter = 2;
		email = concat(key, "@mycompany.com");
		while (!isnull(lookup(LookupTable0,email).email)) {
			email = concat(key, counter++, "@mycompany.com");
		}
	}else{
		email = concat(key, counter++, "@mycompany.com");
	}
	$0.* := $0.*;
	$email := email;
	return ALL
}
5

Excellent!

I didn’t realize that you can create ‘class scoped variables’ in CTL.

Thanks!