Cast data type at the time of inner join

One Star

Cast data type at the time of inner join

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.


Ten Stars

Re: Cast data type at the time of inner join

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

Re: Cast data type at the time of inner join

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.


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


APIs for Dummies

View this on-demand webinar about APIs....

Watch Now