tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

Six Stars

tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

Hello All,

I was trying to collect data from different sources and create a model.

So, In process of doing that, I have employee related data in MySql and Employee SwipeIn and SwipeOut data in MongoDB.

 

I did succeeded by connecting and getting data from both sources and i have used tAggregate to aggregate swipe data.

My Workflow looks like below image

Screen Shot 2019-05-01 at 11.11.44 PM.png

 

In tMap_2 i do have the columns of Date, EmpID.

the output columns of taggregaterow are EmpID, Date, Time, Type

Now, By using tMap3, i am trying to join these two tables columns based on EmpID and Date as left outer join.

 

Before that, to Create Connections between tMap_2 --> tMap3 and tAggregateRow_1 --> tMap_3,

I was able to create connection between  tMap_2 --> tMap3 without any issue.

But when i try to create connection between tAggregateRow_1 --> tMap_3 i couldnt. it is not allowing me to create. then i have to deactivate connection between tMap_2 --> tMap3 and then i created connection between tAggregateRow_1 --> tMap_3 and then i activated connection between tMap_2 --> tMap3.

After all these, i did used left outer join as below image to join these columns.

 

Screen Shot 2019-05-01 at 11.20.42 PM.png

 

Output:-

 

Screen Shot 2019-05-01 at 11.22.56 PM.png

 

As you see in above output image i am unable to get those two columns which i want.

I did tried using tJoin in place of tMap_3 but still i am getting the same result.

I guess it is because of the activity that i mentioned in bold if i am not wrong.

 

Can anyone please guide me where i was going wrong.

 

Thank in advance.

Mohan V.


Accepted Solutions
Community Manager

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

I can *almost* guarantee that your issue is caused by the Dates. No matter how a Date is formatted, when you match with it, it must match to the millisecond. Two dates (2018-10-10 12:00:00.001 and 2018-10-10 12:00:00.000) will not match. My suspicion is that this is what is happening. A way to test this is to print out your values again, but add a date format which will go down to milliseconds. This would be the first place I would look now that I can see your data types.

View solution in original post


All Replies
Community Manager

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

There are 31 rows for the SwipeInsData row and 13 for row7. I suspect that there are no matches. This could be for several reasons. Can you add a tLogRow to each of those rows and let us know what data you get?

Six Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@rhall_2_0 thanks for the reply.

 

I dont think so because, in tMap_2, this is date dimension table where it contains all the dates for each employee

where as in tAggregateRow, there will be only those days where the swipein records were captured.

And as of now, i am jus checking for the single empid i.e 128256.

Please check below images of output for tMap_2 and tAggregateRow.

tMap_2

Screen Shot 2019-05-02 at 8.01.29 AM.png

 

Output For tAggregateRow:-

Screen Shot 2019-05-02 at 8.05.49 AM.png

 

let me know if you need any further information .

 

Thanks,

Mohan V.

Forteen Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@MohanV ,As part of testing I Suggest you to create output files ,one after tMap_2 and other one is tAggregateRow_1 by using two sample jobs and compare those two output files using Talend or some other way like manually,since you have less number of records to know that there are matches or not. I believe there is no matching records or your lookup is not done properly then only you will get nulls from lookup columns.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@manodwhb  thanks for the reply

 

i did tried the way suggested, by putting logRow at each component as below image

Screen Shot 2019-05-02 at 11.54.04 AM.png

 

the values are getting flowed till tMap_3 from tAggregateRow but the left outer join is not working.

tAggregateRow setting:-

 

Screen Shot 2019-05-02 at 12.02.41 PM.png

tLogRow Ouput:-

Screen Shot 2019-05-02 at 12.12.24 PM.png

 

tLogRow_4 Conf:-

Screen Shot 2019-05-02 at 12.10.56 PM.png

 

 

 

tMap_3 Setting:

Screen Shot 2019-05-02 at 12.05.37 PM.png

 

Im still not able to find where exactly it is going wrong.

 

Forteen Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@MohanV ,Can you verify the data between tLogRow_4 and tLogRow_ for the columns which you are doing lookup in tMap_3 ?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@manodwhb  i was going left outer join on Date--> begin_date and EmpID --> emp_id

 

tLogRow Conf:-

Screen Shot 2019-05-02 at 12.32.58 PM.png

Ouput:-

Screen Shot 2019-05-02 at 12.34.00 PM.png

tLogRow_2 Ouput:-

Screen Shot 2019-05-02 at 12.36.49 PM.png

tMap_3 Conf:-

 

Screen Shot 2019-05-02 at 12.29.49 PM.png

Highlighted
Forteen Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@MohanV ,based on the data it should work,I do not see any issue. why cannot create test job as by using the data from tLogRow's to files and in new job create that lookup from files to tMap?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@manodwhb  i did that even.

And it worked cuz the data sources are excel.

and i didnt get that issue where i mentioned in bold in my question that deactivate the links and then create links between tAggreage and tMap and then activate tLog_row4 to tMap_3.

But here i am unable to create the connection jus like that, i have to deactivate the existing link and then i can.

 

I really dont understand where the issue is and why the issue...

 

 

 

Community Manager

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

I can *almost* guarantee that your issue is caused by the Dates. No matter how a Date is formatted, when you match with it, it must match to the millisecond. Two dates (2018-10-10 12:00:00.001 and 2018-10-10 12:00:00.000) will not match. My suspicion is that this is what is happening. A way to test this is to print out your values again, but add a date format which will go down to milliseconds. This would be the first place I would look now that I can see your data types.

View solution in original post

Six Stars

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

@rhall_2_0  Oh My.......

I didn't even think about doing it mannn...

This was the straight point that causing all these things.

 

Thank god.

At last.

i am so thankful you buddy.

 

the dates that i am getting from mongodb are in dd-mm-yyyyy hh:mm:ss (01-04-2019 05:30:00 AM) and the dates that i am getting from Mysql are dd-mm-yyyyy hh:mm:ss (01-04-2019 12:00:00 AM).

And because of this, the join is not working.

 

Community Manager

Re: tMap Left Outer Join is not working for two different data sources data(MySql and MongoDB)

These things catch the best of us out :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

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 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now