topic Cast data type at the time of inner join in Talend Studio

Cast data type at the time of inner join

Anonymous — Thu, 02 Mar 2017 10:53:28 GMT

Hi
I am trying to load and transform data using Talend. I have to extract latest rows based on datetime field. I convert data type using tMap and cast it to Date with format dd-MM-yyyy HH:mm:ss. Performed aggregation, things are moving as expected and my dataset reduced from 10 columns to 4 columns(fields I used to perform aggregation) and rows also got reduced from 24 to 13.

I want to extract all the columns based on the result set return by the tAggregateSortedRow component. But the problem, I am facing is the former one has date format dd-MM-yyyy hh:mm:ss and new filedelimiter has data type string with format (MM/dd/yyyy HH:mm:ss). I used tConvertType but still facing the same issue. PF workflow below.

Can you please help me to get the correct workflow.

Thanks..

Re: Cast data type at the time of inner join

Anonymous — Thu, 02 Mar 2017 12:01:50 GMT

Your data is coming in as a String (unless you are converting it on import using the tFile components). Essentially what you need to do is to convert the String dates to a Date format and then convert them back to a String in exactly the same format as each other. Alternatively you can just ensure that you are comparing dates but you *can* run into issues with rogue milliseconds meaning that they are not exact matches.

Re: Cast data type at the time of inner join

Anonymous — Thu, 02 Mar 2017 15:42:41 GMT

Thanks.. Really helpful.
I am using tAggregateSortedRow to get unique rows but when I am using "Input Rows Count" > 0 then it returns 1 duplicate, if I keep it 0 it suppress 1 row. What is the usage for "Input Row Count"?

I am expecting 13 row, with 0 it returns 12 rows. With > 0, it return 13 rows with 1 duplicate.
Thanks..