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 informa...
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

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.