Post a reply

Write your message and submit

Options

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

moprea
2010-01-07 07:33:34

Right, I suppose I should start brushing up on my Java ASAP.

Thanks again for your help.

-Mircea

shong
2010-01-07 07:19:35

Hello

Where could I have found the "Integer.parseInt()"?

It is Java code, not sql function.wink As you known, Talend product is developed in Java and you are working a Java project.

Best regards

         shong

moprea
2010-01-07 07:15:16

That did it! Thank you very much.

Where could I have found the "Integer.parseInt()"?

It is not available in my expression builder. Is there a list with descriptions some place in Studio?

Thanks again.

shong
2010-01-07 06:49:04

Hello

On the "tLogRow" I cannot set the date pattern because it is an "int".

You need convert date to int, see my screenshot.

Code:

Integer.parseInt(TalendDate.formatDate("yyyyMMdd",row2.date))

Best regards

          shong

moprea
2010-01-07 06:39:31

Okay, that makes sense, thanks.

On the "tLogRow" I cannot set the date pattern because it is an "int". I am copying the date to a date column (know how to do that) and to an int column in the fact table. The copying to the int column is where I am having issues. I am unfamiliar with the what the correct expression should be. 

I cited the SQL Convert example only to show what I am trying to do.

What I need help with is answering how I would convert a date (from the source) to an (8 digit) int (target) to be used as an FK to the Date dim. 

Would this be handled at the expression level? Thanks for your patience.

shong
2010-01-07 05:49:41

Hello

If the latter, am I editing the schema on the component itself?

I mean the latter point, for example:
tMSSQLInput---tLogRow
on the schema of tMSSQLInput, set the date pattern as:
"yyyy-MM-dd HH:mm:ss.sss"
on the schema of tLogRow, set the date pattern as:
"yyyyMMdd"

Best regards

          shong

moprea
2010-01-07 05:47:00

Shong;

Thanks for your response. I am still just a little bit confused.

When you say input/output component (singular) are you talking about the tMap component? Or did you mean the input (data source) and the output (target) components? If the latter, am I editing the schema on the component itself?

Thanks, Mircea

shong
2010-01-07 03:59:03

Hello Mircea

Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS [ENTDATE]  - gets me the desired results in T-SQL.

In Talend, you don't need use the convert function on sql, just define the date pattern on the schema of input/output component.
on the schema of input component, set the date pattern as:
"yyyy-MM-dd HH:mm:ss.sss"
on the schema of output component, set the date pattern as:
"yyyyMMdd"

Best regards

          shong

moprea
2010-01-07 01:08:29

Hello, relatively new to Talend, trying to help out a friend.

Trying to convert SQL Server date time value [2008-09-29 01:56:26.393] into 8 digit int [20080929] for fact table (DateKey) column that is an FK to a Date Dim. Not having much luck with Expression Builder.

So from:

"2008-09-29 01:56:26.393" to "20080929"

Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS [ENTDATE]  - gets me the desired results in T-SQL.

And if possible the same for the FK (TimeKey) Time Dim

"2008-09-29 01:56:26.393" to "015626"

Any ideas or references would be great. The job is in "Java" (not Perl) if that helps.

Thanks, Mircea

c0utta
2009-05-27 07:50:53

sahanakrish, double posting and reviving old threads is frowned upon in most forums.

Morbo
2009-05-27 02:17:17

I don't know about TalendDate.parseDate, but SimpleDateFormat.parse throws exception u could catch and return false otherwhise true.

sahanakrish
2009-05-26 23:38:59

is there any method like isValidDate(<<myString as string>>, <<myDateFormat as string>>) return 0 or 1 for OK or Not OK ?? 
Did you get the answer yet / please share if you do

FeelIT
2008-06-26 08:49:38

ok thx shong


is there any method like isValidDate(<<myString as string>>, <<myDateFormat as string>>) return 0 or 1 for OK or Not OK ??

shong
2008-06-26 08:24:25

Hello

Should I use tFilterRow ?
What is the method to valid the string pattern ?
How should I declare the data type for this field in my source input file ?

Yes, you can use the tFilterRow to filter the invalid date and output them in a file, declare the data type for this field as string. All the valid data in this field should have a unite format, for exmaple the length of them are the same. After filter all the valid data, you can convert a String to a Date. for example,

Code:

TalendDate.parseDate("MM/dd/yyyy",06/26/2008)

Best regards

         shong

FeelIT
2008-06-25 17:06:03

Hi there,

I have a CSV File as a source, one of the field is supposed to be a date

But sometimes, some rows deliver this field with an invalid string for a date  (for example : "378-35-23", or "NaN-NaN-NaN", etc ...)
I would like to test the content of this field to:
1*  Load the correct rows in the target database
2*  Reject the bad rows in an output file       or      Replace the bad data by a default one (NULL or 01/01/1900 for example)

Should I use tFilterRow ?
What is the method to valid the string pattern ?
How should I declare the data type for this field in my source input file ?


thx
Phil

Board footer

Powered by FluxBB