Oracle DB Table Columns to Salesforce Rows

[ishchopra]

Hello Everyone,

I am stuck with one of the design problem in one of the Talend job. Any help or suggestion is welcome.

Situation:

the source table has multiple columns with value Yes or No, however the column header itself map to multiple fields on the salesforce object. I am not sure how to resolve this problem. the table below shows the true representation of the problem:

For Example : PURPOSE_A_POST is a column in the DB but this column itself break into two parts i.e. Purpose A and POST map to Purpose field in Salesforce and Channel field respectively.

please help

A

[TRF]

Hi,

If I understand your question, Salesforce fields Purpose and Channel value depend on which source field is set to "Y". Right?

If so, I'm afraid there is no magic solution for such a case, you need to do it by yourself.

The challenge is to identify the better solution (probably using tMap or tJavaRow to construct the appropriate logic).

Let me know.

[ishchopra]

Hey TRF,

Thanks for the prompt reply, let me explain this with one more example:-

Input File:

PURPOSE_A_POST	PURPOSE_A_EMAIL	PURPOSE_A_PHONE	PURPOSE_A_SMS
Y	N	Y	N

In Salesforce this should be the output, multiple records based on the above

1.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  Post

Status   <Picklist>    :  Opt-in

2.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  Email

Status   <Picklist>    :  Opt-out

3.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  Phone

Status   <Picklist>    :  Opt-in

4.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  SMS

Status   <Picklist>    :  Opt-out

does it make sense?

[TRF · Accepted solution]

As said, nothing magic, you need to use tSplitRow to split input records and populate the desired fields.

Here is an example (limited to the first 4 fields, you need to complete with others).

The job looks like this (tFixedFlowInput just for the test):

tSplitRow is used to generate 1 record per field (4 records in this example, you need to continue with the others 16).

Here is the tSplitRow component configuration with the corresponding schema:

And here is the result:

Hope this helps.

[ishchopra]

TRF,

I cant believe you went to two extra miles to create a job for me, appreciate your time and efforts on this - THANK-YOU. I believe i am pretty near to the solution however one last thing. My input is a Database and when i run the job it pulled 4875 rows, however there are only 108 rows in the database table. Can you suggest how i can restrict these rows? I am pasting the screen shots below:

Secondly, if i want to pull a primary key field, can i pull that as well in the tsplit row?

Job_Status.JPG

[TRF]

You're welcome, you'll thank me when your case will be solved!

1st, as you use a standard tXxxxxInput (Oracle or MS SQL or other) component, you can be sure that if you have 4875 rows in the flows it's because there is at least 4875 records in the database.

I suggest you to verify which table is used be the component or how the select is written.

2nd, yes you can pull all the fields you need but of course, as 1 input record will produce n output records, at the end the values will not be unique anymore. If you want to use it as an external Id on Salesforce side, it should be a problem. In such a case, you can make it unique again if you concatenate an other value with it (for example, let's say PK is this field for both input and output, you can have an expression like row5.PK + "-" + row5.Channel in a tMap just after the tSplitRow component).

Is that what you're looking for?

[ishchopra]

TRF,

Please take a bow, I am marking this as accepted solution. In case i run into problems, i will contact again.

btw where are you based?

A

[ishchopra]

Hey TRF,

Just wondering, do i need to use another tsplit for Purpose B,C,D,E respectively or can i map in a single tsplit?

[TRF]

I suggest you to use a single tSplitRow for this case.