[resolved] Can you set 2 columns to 1 lookup value (tMap)?

One Star

[resolved] Can you set 2 columns to 1 lookup value (tMap)?

I couldn't find a simple answer, so I was wondering if this was do-able at all.
In some other instances I saw people using hashmaps, but I wasn't sure if that was the simplest solution to my problem. Please steer me in the right direction.
I have a SQL database input as main, and a salesforce input as the lookup into a tMap component.
I have one column in the salesforce lookup that I need as the external ID for multiple columns from the database input.
Is there a way to do this with the expression editor, or do I need to create multiple lookups, etc ?
Thank you!

Accepted Solutions
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

After some more searching, I think this is similar to what I'm trying to do
http://www.talendforge.org/forum/viewtopic.php?id=9958
However, I think I tried doing a double mapping and it didn't work.
I'm not following their pictures very well, though.

All Replies

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

Can you give a examples of column name(s) and data types? Are you saying you have to join the two tables, and the salesforce has a column that you have to consider several different columns in the database to match? For example, salesforce has a column like Idaho17 and you have to look at both a "state" and "ranking" column in the database to see if there is a match?
If it's anything like that, yes, I think you'd use an expression. But if you give actual column names and sample data, it's easier to answer concretely.
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

I'll try my best to explain.
I don't know if you know anything about salesforce, but its REALLY picky with the data you have to upload. It has to be a specific 15-digit key so that it recognizes the field.
So here is an example of the table coming out of the database:
Product Grade UniqueKey
1 A 1-A
2 B 2-B
3 C 3-C
4 D 4-D
Now, the fields 'product' and 'grade' are also found in the lookup table from Salesforce. This table references each product and grade with the 15-digit key I mentioned earlier. It's called the DB_Code, and each object has one.
So, the salesforce table columns look like this:
ID Name DB_Code

Which are full of long digit codes it likes. What I'm trying to do in tMAP is to basically point BOTH product and grade to the column of DB_Code, so it can look up those codes as I'm outputting the rest of the data to Salesforce.
Hopefully that made some sense. Let me know if I can clarify better.
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

And if you do know Salesforce, I'm getting the error of "id value of incorrect type" which has to do with mapping those external fields with 15-digits incorrectly.
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

Sorry, I'm still not following.
So you're saying the salesforce table you are trying to load is all 15 digit numbers, like
ID name db_code
888889999955555 626283928562956 592326583729032
837509375750792 293851985847423 704710983742329

The lookup table from data force has something like
Grade Product DBCODE
B 8 606084260608423
B 9 329083892839700
And your data from the database is like
Grade Product ColumnA COlumnB
And you want to load maybe ColumnA, ColumnB, and the corresponding DBCode from the product table?
If that's the kind of thing you're trying to do, that's easy enough, let us know if that's it or what's different.
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

That sounds close to what I want to do. Sorry this is so hard to explain.
I've gotten a better grasp on what should be happening since I last replied, because I got it to load into Salesforce, I just still have the problem of not knowing how to map 2 elements with 1 lookup value.
I also got clarification from the salesforce administrator on which values actually have the 15 digits. I was trying to abridge the tables before but let me just fill in some sample data this time.
In the database table I am pulling, there are several slots, but we are only worried about 2:
Product Grade
1 A
1 AA
2 B
3 C
4 D
4 S

In the lookup table, the slots we need to use are:
DBCode ID
1 357492758393848
2 757592837592850
3 452983525672903
4 2983928357928353

The output that I need in Salesforce looks like (I think..) :
Product Grade Unique Key
Siding A 1-A
Siding AA 1-AA
Roofing B 2-B
Window C 3-C
Shingle D 4-D
Single S 4-S
Now, each of the DBCode numbers corresponds to a product (siding, roofing, etc) which is found in the lookup table with the special ID. Salesforce knows that the ID matches up with a certain product.
Right now in talend, I am mapping the field 'Product' from the database to the 'DBCode' in the lookup table, and then mapping the 'ID' to the output of 'Product' AND 'Grade', because both need the special ID in the field. When it gets into Salesforce, the data looks like:
Product Grade Unique Key
Siding Siding 1-A
Siding Siding 1-AA
Roofing Roofing 2-B
Window Window 3-C
Shingle Shingle 4-D
Single Shingle 4-S
Understandably because I'm not sure how to map BOTH 'Product' and 'Grade' onto the one field 'DBCode'

Hopefully this makes more sense?
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

After some more searching, I think this is similar to what I'm trying to do
http://www.talendforge.org/forum/viewtopic.php?id=9958
However, I think I tried doing a double mapping and it didn't work.
I'm not following their pictures very well, though.
One Star

Re: [resolved] Can you set 2 columns to 1 lookup value (tMap)?

After trying again, the plan in the link I previously posted worked!
There were just so many connections I must have mixed up some on the first attempt.
I drew out a picture to help me get it right this time.
Thank you Levin for all your help.
I hope anyone that has this problem in the future can find it now, when I was first researching this question the link I posted did not appear.