topic Re: Conditional mapping using lookUp input in Talend Studio

Conditional mapping using lookUp input

Anonymous — Sat, 16 Nov 2024 06:12:05 GMT

Hello,

I have a case that i've been trying to implement for some time but i dont seem to be able to wrap my head around this. Hopefully you can be of some help.

I have an input file and a lookup Input which is a salesforce object containing a contractNumber.

My case is that i want to do a conditional check of the contractNumber of the input lookup against a number which is in input file. If its a match, i want to input the matched number, otherwise another number should be the input. Is this possible?

I've tried doing expressions such as "row1.jobNumber == row7.ContractNumber ? row1.jobNumber " but this does not work as row7.ContractNumber is not recognized as a variable.
(Row7 is the input lookup object, Row1 is the input file).

(Not the expresison i wrote, but just to give you an idea of what im trying to do..)

How could i go about solving this? Any idea would be much appreciated!

Thanks for reading.

Re: Conditional mapping using lookUp input

DataTeam1 — Sat, 30 Mar 2019 19:35:42 GMT

Hi @deldel131

I didn't understand what you would like to achieve.

Can you give some examples of input data (row1 and row7) and output data you want to get?

Re: Conditional mapping using lookUp input

TRF — Sun, 31 Mar 2019 07:37:52 GMT

Hi,
1rst of all, you should never use == to compare 2 strings. Use String.compare() instead.
2nd you cannot use an "if" expression un the place you use it. Use a ternary expression instead.
And finally to solve your case, ude a left outer join on your tMap (between row1.jobNumber and row7.ContractNumber) then on the right side of the tMap (output) you can do what you want for the Name field with the following expression:
row7.ContractNumber == null ? row1.jobNumber : row7.ContractNumber
Hope this help.

Re: Conditional mapping using lookUp input

DataTeam1 — Sun, 31 Mar 2019 19:31:45 GMT

@TRFyou are absolutely right but the conception of @deldel131 problem seems to be illogical or I don't understand it.

When you have values of row1.jobNumber as bellow:

and values of row7.ContractNumber as bellow

And when you use left outer join (main row1 and lookup row7) and use row7.ContractNumber == null ? row1.jobNumber : row7.ContractNumber

you will have:

1 - 1 => 1

2 - 2 => 2

3 - null => 3

4 - null => 4

5 - 5 => 5

So IMHO it doesn't give us expected effect. In this case it is very important to see an example of what @deldel131 want to achieve.

Don't hesitate to give kudos when a reply is helpful and when your query is answered, please mark the topic as resolved.

Re: Conditional mapping using lookUp input

Anonymous — Sun, 31 Mar 2019 20:01:40 GMT

Thank you very much for your answers, I realise that my explanation of the situation wasn't the most clear so I'll give it another shot.

I have a local file with some data - it looks something like this:

<JobNumber>M2019/2042</jobNumber>
<Text14>M2019/2045</jobNumber>
<SpecificationName>32149</SpecificationName>

...

The data of this file is stored in row1 in the tMap.

row7 is a Salesforce input lookup.
So the case is:

Pseudocode -
IF row1.JobNumber == row7.ContractNumber
set SalesforceContractNumber to row7.id
else if row1.Text14 == row7.ContractNumber

set SalesforceContractNumber to row7.id

else

null

How the map currently looks:

I hope this clarifies the problem.

I greatly appreciate you taking your time looking at this and giving me input. Because I'm feeling a bit lost!

Re: Conditional mapping using lookUp input

Anonymous — Sun, 31 Mar 2019 20:08:03 GMT

Hi TRF,

Thanks for the reply.
The comparison was definitely wrong, if i do a comparison with a test string where i know its a match it does work. However doing the comparison for the variable row7.ContractNumber does not.

For instance, i tried the expression row1.JobNumber.equals(row7.ContractNumber) ? row7.Id : null
With this expression it always return null, even when i know that there should be a match.

I'll keep at it.

Re: Conditional mapping using lookUp input

DataTeam1 — Sun, 31 Mar 2019 21:28:56 GMT

@deldel131 here is an example of the solution:

In tMap_1 I compare row1.JobNumber to row7.ContractNumber (notice that I use Inner Join type).

Everything else I send to reject flow

In the next step I compare Text14 (from last reject) and every thing I send to next reject.

So now I hav 3 outputs and I send data from its into tHashOutput (this component is hidden in palette so you have to unhide it (File => Edit project propertis => Designer => Palete settings => Technical) )

Of course I append data.

In the last step I present whole data set (you can see it in tLog_row_4)

My data example

Data in row1 (tFixedFlowInput_1):

Data in row7 (tFixedFlowInput_2 and tFixedFlowInput_3)

Don't forget to give kudos when a reply is helpful and when your query is answered, please mark the topic as resolved.