topic Re: Expression Builder - Test Variable Value issues in Talend Studio

Expression Builder - Test Variable Value issues

Anonymous — Sat, 16 Nov 2024 12:13:44 GMT

In a tMap I have data coming in as a Integer(32) that is being mapped to a MySQL database as an Integer Int(32) of about the same size. An Int is supposedly 2,147,483,647 to -2,147,483,647 or something like that.
Use Expression:

row1.Column1

Go to Test variable section, set

row1.Column1 = 06232012

* No quotes etc.
Click the button 'Test' and you get the result:

I basically just want to change this date number into a new format but the test window in the expression editor appears to be buggy. It is in a excel MMddyyyy format and i need a yyyyMMdd int format. I first thought it would be a simple import as String format and then use:

row1.BILL_DATE.length() != 8 ? "" : Integer.parseInt((
row1.BILL_DATE.substring(4, 4) +
row1.BILL_DATE.substring(0, 2) +
row1.BILL_DATE.substring(2, 2)
))

.. but it keeps failing. I have tried several iterations of this as a number / string / etc.. to no avail. Please let me know if I am doing something Wrong here -- I am not sure if this is a bug or if I am testing wrong.

Re: Expression Builder - Test Variable Value issues

Anonymous — Sun, 29 Jul 2012 14:18:25 GMT

hi,
first read integer as a String :

(String.valueOf(06232012))

then analyse the format of the string as a date

parseDate("MMddyyyy",(String.valueOf(06232012)))

and finally format the result in a new format :

formatDate("yyyyMMdd",parseDate("MMddyyyy",(String.valueOf(06232012))))

that's it
hope it helps
regards
laurent

Re: Expression Builder - Test Variable Value issues

Anonymous — Mon, 30 Jul 2012 16:50:12 GMT

So you are saying this is a bug in the expression builder trying to be too helpful which is akin to Excel automatically converting big numbers to scientific notation?

Re: Expression Builder - Test Variable Value issues

Anonymous — Tue, 31 Jul 2012 07:55:49 GMT

hi,
made a little mistake as if you read '06232012' as an integer ... you'll lost '0' => '6232012' ..
So take it like an String to keep all characters.
and to be honest i never use Test button in tmap but debug (&test) values with the debug run (in Tis you are be able to see the values using in tmap in & out
using substring to rewrite your date, how can be sure your date will always be MMddyyyy format.
To conform your data you'll have to check MM, dd and yyyy are valide before format in another way or you'll never be sure that those values are coherent (some data quality)!
Never trust sources
For your purpose i'll parse date, check date and format date with Talend method & component !
regards
laurent