How to get tMap to join Between values?

One Star

How to get tMap to join Between values?

Hello all
Here's my scenario: I have one table that lists financial account ranges and descriptions (e.g. "Payroll" accounts range from 51110 to 51199, "Parts" accounts range from 61250 to 62250, etc.) and a second with account transactions (e.g. 1/5/2010 entry of $1,000 for account 51115, 2/1/2010 entry of $500 for account 62000, etc.). I need to assign the transactional data the appropriate payroll description. I'm basically building staging tables for operating statement analysis... someone else must have done this (or something similar).
How can I join based on ranges as opposed to discrete values? How do I specify this in the tMap editor?
Thanks in advance - I appreciate the help
- Russell
Seven Stars

Re: How to get tMap to join Between values?

See screenshot
One Star

Re: How to get tMap to join Between values?

Hi Alevy
Thank you for the reply. When I try that, I get errors that (following your example table name) say "row1 cannot be resolved" and "row2 cannot be resolved." I'm posting some screen shots to clarify. Also, since I'm doing text field comparison the join is a bit more complex.
Thanks for the help
- Russell
One Star

Re: How to get tMap to join Between values?

Hey guys,
i face quite the same issue.
I want to model a SQL left join in tMap like this:
SELECT a.FullString,
b.MatchString
FROM a LEFT JOIN b
ON a.FullString LIKE b.MatchString
So i have to simulate the "like" operator in the inner join match condition? If i do that - would be "a.Fullstring.contains(b.MatchString)" - i get the same error as the thread-opener.
I tried a workaround by activating the output expression filter (see screenshot). This works as a replacement for an inner join, but how to have _all_ rows processed as a left join operation?
Thanks
dexter
Seven Stars

Re: How to get tMap to join Between values?

I get errors that (following your example table name) say "row1 cannot be resolved" and "row2 cannot be resolved."

You need to use the names of your tables. In my example, you can see those are row1 and row2. In your example, they are F0902F0901Joined and StgF8305Flat.
As in my example, your expression needs to be in the expression box, activated by clicking the white arrow and green plus icon on the lookup table in tMap. It's off-screen in your screen-print; you need to widen the left section of tMap to see it.
By having the expression against the R5OR01 field, you are saying that the value in that field must match your expression but your expression is a boolean result.
BTW, you should not need to use StringHandling.TRIM function if you check the Advanced Setting for your input components to trim the appropriate field(s).
Seven Stars

Re: How to get tMap to join Between values?

how to have _all_ rows processed as a left join operation

Talend doesn't seem to properly deal with joins where none of the field values of the lookup table are directly specified. I coincidentally raised a BugTracker (#0012344) last week about that very issue.
For now, I suggest you add a dummy field to your lookup table (you can do that using the SQL input statement) with a value that you can hard-code in the tMap. That will make the "Inner Join" check-box available, which, if left blank, will make your tMap work correctly as a left-join.
One Star

Re: How to get tMap to join Between values?

Hi alevy,
thanks for your quick hint! Your suggestion sounds like a clever workaround, i will try that.
Meanwhile i helped myself with a sql query running in a tXXSQLRow component that triggers on success of a subjob. This is a clean solution but i would prefer the tMap approach to have graphical representation and to operate further on the data stream.
Thanks again
dexter
One Star

Re: How to get tMap to join Between values?

Alevy - Thank you... the expression box was hidden, even with the tMap UI in full screen. Entering my criteria there got me past that bump in the road.
- Russell
One Star

Re: How to get tMap to join Between values?

Hi.
I want to do the same thing :
"
SELECT a.FullString,
b.MatchString
FROM a LEFT JOIN b
ON a.FullString LIKE b.MatchString
"
On the tmap, i put the input filter "a.FullString.contains(b.MatchString)" on the lookup link.
My problem is that my result is an INNER JOIN instead of an LEFT OUTER one.
I even tried with a dummy field to have at least one "proper" join in the Tmap, but without success.
Someone has a solution ?
Thank you very much,
jeremy.
Moderator

Re: How to get tMap to join Between values?

Hi ferreiraj,
Could you please post your tMap editor screenshot into forum? Is there any extra space in your input source?
Elaborating your case with an example with input and expected output values will be preferred.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to get tMap to join Between values?

Hello.
Thanks for the reply.


With usCity data :
====
TITLE
====
Denver
Springfield
Boise
Topeka
Denver
==
And houseData :
=========
CITY | HOUSE
=========
enver | 1
=========
I would like to have as result, with a "like" :
.-----------+-----.
| finalDataLog |
|=----------+----=|
|Title |House|
|=----------+----=|
|Denver |1 |
|Springfield|null |
|Boise |null |
|Topeka |null |
|Denver |1 |
'-----------+-----'
But what i got instead :
.------+-----.
|finalDataLog|
|=-----+----=|
|Title |House|
|=-----+----=|
|Denver|1 |
|Denver|1 |
'------+-----'.
What am i doing wrong ?
Please apology my crude english,
Regards,
One Star

Re: How to get tMap to join Between values?

Hi.
As a workaround, I will store my usCity data into a sgbd then, do that inner join between my table and my houseData, then do a outer join in a tmap between my usCity data and my table.
It's a bit tricky but I think it's the easiest solution, if the tmap is really unable to make a Left outer join with a "like".
regards,
jeremy.
Four Stars

Re: How to get tMap to join Between values?

I want join put condition for fetching record between two table , this is hive where clause 
here i want only last 91 days record 
s and d are two table which will be join as cross join 
condition is s.invoicedt between date_sub(d.date1,91) and d.date1
how to implement in tamp?