One Star

Expression Builder - Test Variable Value issues

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:
1651722

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.
3 REPLIES
Seventeen Stars

Re: Expression Builder - Test Variable Value issues

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
One Star

Re: Expression Builder - Test Variable Value issues

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?
Seventeen Stars

Re: Expression Builder - Test Variable Value issues

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 Smiley Happy
For your purpose i'll parse date, check date and format date with Talend method & component !
regards
laurent