topic Re: tmap joining 2 tables in Talend Studio

tmap joining 2 tables

Moe1 — Sat, 16 Nov 2024 04:22:43 GMT

Im joining 2 tables in tmap based on key (ID) and timestamp range....

Table 1

username

userID

timestamp

------

Table 2

username

userID

creationdate

in tmap (im linking userid to userid) and I need to link timestamp to creationdate but (the creationdate might be equal to any range of timestamp field +/- 1 hour).. how to do it in tmap.

Re: tmap joining 2 tables

TRF — Tue, 15 Oct 2019 14:30:32 GMT

Use a classic inner join based on UserId and then add a filter expression on the output flow to exclude records not responding to your rule. Using tMap local variable to compute timestamp +/- 1 hour should help.

Re: tmap joining 2 tables

Moe1 — Tue, 15 Oct 2019 15:00:48 GMT

Thank you, you mean like the attached screenshot?

2.jpg
1.jpg

Re: tmap joining 2 tables

TRF — Tue, 15 Oct 2019 15:13:11 GMT

Looks fine.
You just have to validate the expression filter.

Re: tmap joining 2 tables

Moe1 — Tue, 15 Oct 2019 15:26:06 GMT

Thanks, I need one more thing.. I also need to add to the expression filter another field, I need to match a field from table 1 to table 2, but the field in table is a subset from the field in table 2:

Table 1

agent

Table 2

Description

Description format is like this ip pipe agent but it also store -1 or N/A etc...: IP|agent

I need to check if the field contains IP|agent, I need to remove everything from the pipe backwards and compare everything after the pipe.

Re: tmap joining 2 tables

TRF — Tue, 15 Oct 2019 15:46:35 GMT

Use a tMap local variable to remove the beginning of the string with the following expression:

row1.description.replaceAll("^.*\|", "")

(Try without the | or with double \ if it doesn't work).

Then reuse the variable in your tMap output filter expression.

Re: tmap joining 2 tables

Moe1 — Tue, 15 Oct 2019 15:56:07 GMT

It seems that my date range function using talenddate is not working, any ideas?

Re: tmap joining 2 tables

TRF — Tue, 15 Oct 2019 16:19:12 GMT

Maybe you can try with diffDate:

Math.abs(TalendDate.diffDate(TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", row2.CREATIONDATE), TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", row1.m_timestamp), "HH")) > 1

Depending of what you want, change "> 1" by "<= 1" or something else.

Should work (but not tested).

Re: tmap joining 2 tables

Moe1 — Tue, 15 Oct 2019 16:49:09 GMT

Thank you, I will try to test the solutions provided by you by tomorrow. I still have one question as Im not the DB owner nor admin, is there a way to deal with not indexed columns in DB, for instance the timestamp and creationdate columns are not indexed and I guess it will take ages to execute as there are around 20 million records.

Re: tmap joining 2 tables

TRF — Tue, 15 Oct 2019 16:58:27 GMT

Hum... this is a new question.
Try first to solve the initial case.
Then regarding the volume, you'll probably have memory issues...
If you have 20 millions records into the lookup table, loading all the records at once could be difficult.
There is no problem regarding the fact there is no index on the "date" fields as soon as you don't use them in your SQL queries.
If you do, you should negotiate the appropriate index with your preferred DBA.