Topic: Javascript Help for Case Conversion

2011-09-16
05:23

Javascript Help for Case Conversion

Hi,

I'm a new user of Datacleaner, and am exploring normalizing & cleaning my set of data.

Was trying to find a way to capitalize all my text in my data fields, and came upon an earlier post in forum [http://datacleaner.eobjects.org/topic/193/Alphabet-case-conversion]

I did the exact same code input, and ran into an error when I preview output

Error as follow:
ReferenceError: "Name" is not defined. (JavaScriptTransformer#4)

I only selected a data field say 'Course Name' . How should i go about declaring the var to use the data in the corresponding field?

A pity there is no documentation on how to use this, as i believe this function coupled with javascript will give a lot more features than current software can provide. I'm just a beginner in javascript as well.. btw..

Thanks again for any help!

PS: I am reposting as it seems a reply to old post will not be reflected in forum!

Reply by
kasper

2011-09-16
05:44

Hi wsquare,

To reference a column name that is not possible as a Javascript variable, you can use the "values" array. This will reference the columns in the order you've selected them above, so if you only select "Course name" in the checkboxes above, then you can reference it like this:

var contactName = values[0];

Reply by
kasper

2011-09-16
05:44

PS: The forum sends out notifications to the datacleaner-notify mailing list as well as to any previous posters of a topic, so your reply was distributed at least to those who has interacted in the other topic and to those who have a general wish to monitor the forum :)

Reply by
wsquare

2011-12-05
04:25

hi,

I'm back to this. If I want to apply the same scripting code to few selected columns, how should it work?

 

var contactName = values[0];
var contactName1 = values[1];
var contactName2 = values[2];

return contactName.toUpper();
How do i do so for the other columns?
I tried repeating the return lines with the different variables.. it didnt work. 

Pardon me for this abhorrent logic.

Reply by
kasper

2011-12-05
04:24

Hi wsquare,

The Javascript transformer can only return a single value, so if you want to return multiple, then you need to add more transformers.

Reply by
wsquare

2011-12-05
04:25

Oh. So basically the multiple columns work, if i only return a single value ie

contactName = values[0]+values[1]..
...
return contactName
?

I have done as what you have proposed, but running foul with a problem.

var surname = values[0];
  var upper = surname.toUpperCase();

Cannot call method "toUpperCase" of null (JavaScriptTransformer#5)as i have null values..

I tried a workaround for this:

 
  if surname.replace(/\s,"")=="" 
	surname =="" 
  else var upper = surname.toUpperCase();

And i have another error arising :
java.lang.IllegalStateException: Could not invoke initializing method public void org.eobjects.analyzer.beans.script.JavaScriptTransformer.init()

Could not invoke initializing method public void org.eobjects.analyzer.beans.script.JavaScriptTransformer.init()

null

missing ( before condition (JavaScriptTransformer#5)

How can i troubleshoot this error :(

Thanks again!

Reply by
kasper

2011-12-05
04:25

Hmm the init() issue you are seeing is because the Javascript has syntactic issues.

But coming back to your initial solution, I would recommend just checking for nulls, like this:

var surname = values[0];
var upper;
if (surname == null) {
  upper = null;
} else {
  upper = surname.toUpperCase();
}
return upper;

Reply by
wsquare

2011-12-06
06:53

Hi,

Thanks. I've made the necessary recitification and it works!

Generally, i did 3 separate Javascript transformers using the same code.

Everything works fine until I tried to concatenate them together.

ie. null field turn out to have 'null' word stored in the transformed field?

So if i pieced two fields : ALAN null => "ALAN null"

Shouldn't it be empty instead?

Hm, sorry is there something wrong that I've done? Thanks

Reply by
kasper

2011-12-06
06:53

unfortunately, that is just how Javascript is designed when you concat nulls with strings!

Maybe you can make a small utility function like this:

function strValue(str) {
  if (str == null) {
    return "";
  }
  return str;
}

and then use it when getting your values...

var surname = strValue(values[0]);

Good luck

Reply by
wsquare

2011-12-09
04:23

hi kasper,

Need your expertise advise in this as none of my workarounds work :(

1. I did another script transformer on my concatenated output field ie:

 gname=values[0];
 return gname.replace(/null/g,"");

2. Another workaround I tried is your utility function plus a mixture of others. All failed

I tried to assign a new value 'emtpy' if null field is detected, but somehow, it just refused to assign the new value..

Similar to this :

  var fn = values[0];
  var mn = values[1];
  var empty = "empty"
if (mn==null) {
   mn = empty;
}

return fn +" "+mn;

output still appears null all over.. :(

Any idea what's wrong? Thanks again..

Reply by
kasper

2011-12-09
04:36

Hi wsquare,

I tried this in the example database (table=EMPLOYEES, columns=OFFICECODE+REPORTSTO) which has a few null values:

function eval() {
  var fn = strValue(values[0]);
  var mn = strValue(values[1]);
  return fn +" " + mn;
}

function strValue(str) {
  if (str == null) {
    return "";
  }
  return str;
}

eval();

And that works fine

Reply by
wsquare

2011-12-09
04:36

Also through this series of troubleshootings, i discovered that there was abnormality in data import from my excel sheet?

My data rows have no empty spaces, but after import, take a look at the attached picture link :

[http://imageshack.us/photo/my-images/823/mnempty.jpg/]

There are 'null' indication, when the rest are all blank field.

Hm, i'm kinda bewildered. Any help?

Thanks

Reply by
kasper

2011-12-09
04:36

Hi wsquare,

This is because there is in data processing a significant difference between NULL and empty string. NULL means "no value" (eg. the spreadsheet file format does not even mention that cell's content) and empty string means ... yea, well, an empty string value!

In the script I posted before, NULLs are being converted to empty strings. Maybe you want to convert further?

Reply by
wsquare

2011-12-09
04:36

Hi kasper,

Thanks for getting me on track. I discovered my field of feeding mn is the output of this code :

  if (name == null ){
  
  punctuationless = null;
  }
  return punctuationless = name.replace(/\s{2,}/g,"")

weird thing is i did a minor change to the code :

if (name == null ){
  
  punctuationless = "";
  }

and the code works well by not showing null after concatenation!

Saved my day. :)

Reply by
wsquare

2011-12-09
04:23

i think i got what you mean regarding null and empty string "" through this series of workarounds i've done!

Reply by
kasper

2011-12-09
04:23

Happy that you got it working :)

Reply by
wsquare

2011-12-09
04:36

awesome. i'm on track now. thanks alot kasper!

I took me awhile to work out the js expressions such as removing punctuation marks, doing word count of a string etc. Simple ones like converting cases of string

All these were done using javascript transformers. It may be good to consider such string transformers in future release.

And i'm just using all these basic transformers to assist me in cleaning name data fields.

Log in by clicking the login link at the top of the screen

Topic: Javascript Help for Case Conversion

Javascript Help for Case Conversion

You need to be logged in to participate