Normalizer not working correctly

Hi,

I am attempting to normalize some data, and am finding an issue I can’t seem to get around.

I am not doing anything overly complicated. Basically I would like the following to happen:

I would like to normalise lines like this -

1000|admin - service - query - status|matty
2000|domain - user - group|frank

To this -

1000,admin,matty
1000,service,matty
1000,query,matty
1000,status,matty
2000,domain,frank
2000,user,frank
2000,group,frank

But this isn’t happening. My count seems to be working (there are different numbers of attributes in each field I have ‘split’), as it returns the correct number of attributes for each field. But it doesn’t seem to be clearing the list. So, I end up with this…

1000,admin,matty
1000,service,matty
1000,query,matty
1000,status,matty
2000,admin,frank
2000,service,frank
2000,query,frank

And strangely, the example normaliser does the exact same thing!

I have tried to work out how to clean the list variable, ‘usg’, but can’t get that to work…

Any help would be much appreciated!!

Here is my code…

list usg;


function transform(idx) {
	usg = split($usg,"\\s*-\\s*");
	$0.usg := usg[idx];
	$0.user_login := $0.user_login;
	$0.name := $0.name;
	$0.sec_level := $0.sec_level;
	$0.sql_access := $0.sql_access;
	$0.application := $0.application;
	$0.psid := $0.psid;
}

function count() {
	return $usgcount;
}

And these are snippets of my clean attempts. Neither have worked as a list won’t take an integer or string value… This may not be the solution anyhow…

function clean() {
	usg='';
}

and integer

function clean() {
	usg=0;
}

Thanks!

Hello Mattyslater,

We suggest you use the following code as this will work properly.


//First, you must declare your variables as global ones.

list List;
int Count;

//Second, you should create the list within the count() function and count its length using the length() function. This length will be //returned by the count() function.

function count() {
	List = split($0.fieldtobesplit,"\\s*-\\s*");
	Count=length(List);
	return Count;
}

//Only after that, you will need to map input fields to output fields that should be let as they were, and also map individual elements of the //list that have been created within the count() function to the output field that should be split.

function transform(idx) {
	$0.fieldtobelet1 := $0.fieldtobelet1;
	$0.fieldtobesplit := List[idx];
	$0.fieldtobelet3 := $0.fieldtobelet3;
        ..............................................
	$0.fieldtobeletN := $0.fieldtobeletN;
}

//Before the next input record is parsed - when the count() function will be applied again and maybe will return different value - you need to //clean older values of the global variables, both Count and List. 
//Remember that each list must only be cleaned either using the remove_all() function with the list as its argument, or by assigning: //List=null;

function clean(){
	Count=0;
	remove_all(List);
}

As regards our examples, we are preparing new examples projects that will be available in the future.
There are already two new examples projects (CompanyTransactionsTutorial and CTLFunctionsTutorial) in the newest CloverETL 2.7/2.2 that was released yesterday.
There you can find much useful information. Especially, if you want to learn more about our CTL functions.

We also suggest you read our Clover_users_manual.
It can be found at: http://www.cloveretl.com/documentation/ together with other two documents.

Best regards,

Tomas Waller

In addition to the mentioned above, for detailed information about Clover Transformation Language see:
http://www.cloveretl.com/_upload/clover … /ch23.html

For details about CTL functions see also:
http://www.cloveretl.com/_upload/clover … k/apf.html

and you can run any of the graphs contained in our CTLFunctionsTutorial.

Hi Tomas,

You are a fantastic man! I really appreciate both your fast help and depth of knowledge. Your solution worked perfectly…

Are you part of the dev team for clover?? I gather so from your mail. If there is any help I can offer as far as testing in the field, let me know. I am an identity architect and hoping to make CloverETL part of my customer solutions.

Grabbing the new version and having a play around will be my first port of call! :wink:

Regards,
Matt.