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

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

Anonymous — Sat, 16 Nov 2024 05:56:51 GMT

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

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.

Output:-

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.

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

Anonymous — Wed, 01 May 2019 23:09:10 GMT

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?

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

Anonymous — Thu, 02 May 2019 03:39:34 GMT

@rhall 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

Output For tAggregateRow:-

let me know if you need any further information .

Thanks,

Mohan V.

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

manodwhb — Thu, 02 May 2019 05:19:38 GMT

@Moe ,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.

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

Anonymous — Thu, 02 May 2019 07:46:40 GMT

@manodwhb thanks for the reply

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

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

tAggregateRow setting:-

tLogRow Ouput:-

tLogRow_4 Conf:-

tMap_3 Setting:

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

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

manodwhb — Thu, 02 May 2019 07:58:11 GMT

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

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

Anonymous — Thu, 02 May 2019 08:08:00 GMT

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

tLogRow Conf:-

Ouput:-

tLogRow_2 Ouput:-

tMap_3 Conf:-

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

manodwhb — Thu, 02 May 2019 10:06:37 GMT

@Moe ,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?

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

Anonymous — Thu, 02 May 2019 10:56:03 GMT

@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...

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

Anonymous — Thu, 02 May 2019 11:11:28 GMT

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.

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

Anonymous — Thu, 02 May 2019 12:05:45 GMT

@rhall 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.

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

Anonymous — Thu, 02 May 2019 14:45:50 GMT

These things catch the best of us out 🙂