tmap joining 2 tables

[Moe1]

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.

[TRF]

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.

[Moe1]

Thank you, you mean like the attached screenshot?

2.jpg
1.jpg

[TRF]

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

[Moe1]

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.

[TRF]

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.

[Moe1]

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

[TRF]

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

[Moe1]

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.

[TRF]

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.