topic Re: How to handle this scenario in Talend Studio

How to handle this scenario

pari1 — Fri, 10 May 2019 15:03:42 GMT

Spoiler

Getting multiple records in the output instead of exact result set in the above image

Re: How to handle this scenario

Anonymous — Fri, 10 May 2019 15:38:33 GMT

Multiple outputs would be correct if you are joining on Oppr_Id. I would expect 9 outputs.

Re: How to handle this scenario

pari1 — Sat, 11 May 2019 07:35:58 GMT

Thanks for you reply.
I need only 3 records as output

Re: How to handle this scenario

Anonymous — Sat, 11 May 2019 17:16:51 GMT

I understand that, but there needs to be some rules before an answer can be given. I see that S1 needs to match C1, S2 needs to match C2 and S3 needs to match C3 in your example. However, I presume that they are example values. I also understand that nulls are not required. What is the rule that prevents S1 matching C2 and C3, S2 from matching C1 and C3 and S3 from matching C1 and C2? The only join field you have is the Oppr_Id. We need the rules and how they should work with real data.

I can tell you how to get the result you have got. You match on Oppr_Id and then take number character from the other two fields and match on numbers. To do this you could use substring(). But I sense that is not what you really want.

Re: How to handle this scenario

pari1 — Sat, 11 May 2019 17:28:12 GMT

Thanks for you reply.
Below is the scenarios :
1. Opportunities is related to services
2. Opportunities is related to contacts
3. Opportunities is related to roles
There is no relation between services ,contacts & roles.
When am trying to arrange this am getting kind of cross product that means multiple records. So how I need the output is based on the maximum count of either services or contacts or roles, I need the opportunities count.

For example: S services is 4, R roles 2, Ccontacts 2
So how my output looks

O1 S1 R1 C1
O1 S2 R2 C2
O1 S3 null null
O1 S4 null null

Re: How to handle this scenario

Anonymous — Mon, 13 May 2019 10:21:54 GMT

Can you give an example like the first one, but with all of the rules. You have 4 columns in your explanation, but only 3 in your original example. Also, your rules are not shown by your explanation. You say....

"1. Opportunities is related to services
2. Opportunities is related to contacts
3. Opportunities is related to roles"

But your example shows this....

"O1 S1 R1 C1
O1 S2 R2 C2
O1 S3 null null
O1 S4 null null"

Opportunities is the first column. O1 relates to S1, R1 and C1 in the first row. But it doesn't relate in any of the other columns.