Merge two tables based on multiple criteria

[JanosIT]

Hi there,

I’ve been trying to find a solution for our use case with no success, so I’d like to ask the Community for help.

We have two tables (both from Salesforce), table A contains deal information, table B contains campaign information.
Both table A and B have the following fields: Start Date, End Date, Client1, Client2, Area Code, ID

I would like to create a new column in in table B, called “DealId”, and copy the ID field from table A, if all of the following criteria are met:

• Start Date (B) is equal or greater than Start Date (A) and
• End Date (B) is equal or less than End Date (A) and
• Client1 (B) = Client1 (A) and
• Client2 (B) = Client2 (A) and
• Area Code (A) contains Area Code (B)

So far I’ve tried the Mapper Snap and putting all criteria as an expression but it didn’t work. I have been also experimenting with the Join and Conditional snaps, but unfortunately I still haven’t found any solution yet.

Right now in my pipeline I have two filtered documents, but cannot find a solution to add the right deal IDs to campaigns.

Please help!

[SpiroTaleski · Accepted solution]

Hi @JanosIT

One of the possible solutions could be:

• Concatenate Client1 and Client2 fields and use them as a key while joining

• Use Filter Snap to implement the rest of the criteria

    -Start Date (B) is equal or greater than Start Date (A)
    -End Date (B) is equal or less than End Date (A)
    -Area Code (A) contains Area Code (B)
  

Regards,
Spiro Taleski

[koryknick]

Welcome to the Community @JanosIT!

I agree with @Spiro_Taleski - in this case, I would probably use a Join snap with join conditions of Client1 (B) = Client1 (A) and Client2 (B) = Client2 (A), then use a Filter snap with the remaining conditions that aren’t strictly based on equality.

Remember to use sorted input in the Join to keep memory requirements low, especially since there will be a Cartesian product effect since it sounds like both streams may have multiple records based on that join criteria.

[JanosIT]

Thank you @Spiro_Taleski and @koryknick for your help, I was able to get the expected results by using your ideas.