Merge two tables based on multiple criteria

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!

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

1 Like

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.

1 Like

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

1 Like