Forum Discussion

JanosIT's avatar
JanosIT
New Contributor II
5 years ago
Solved

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!

expression_language
pipeline
  • SpiroTaleski's avatar
    SpiroTaleski
    5 years ago

    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

3 Replies

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    5 years ago

    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's avatar
    koryknick
    Employee
    5 years ago

    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.