Skip to main content
Solved

The merge of data - pattern for merge

  • November 1, 2022
  • 1 reply
  • 101 views
M
Star Blazer L2
Forum|alt.badge.img+2

Hi,

Have gotten some good answers here. So here is a case. It is a requirements to merge data.

This involves steps:

  • Compare to sets of data - say Target and Source.
  • Any record not in Target but in Source will be added (insert).
  • Any record in Target but not in Source will be flagged ‘deleted’ or deleted (delete)
  • Any record in both sets will be updated - say a timestamp will be updated (update)

In SQL it is known as MERGE.

One way would be an full outer join and then use filters, with three branches in the plan, but I hope I miss an easier way ;)

 

Best answer by BrianF

Hi Marnix,

If the keys/values match exactly, depending on use case, you can use an outer join in a Join step over your source and target tables, and set a column to flag rows with the required INSERT, UPDATE, DELETE operation, based on whether there are matching values in in_left.key and in_right.key or just one or the other.  You could then use Extract Filter and/or Filter steps to get lists of records for each operation.


Note that if you are referring to doing this on a database, there is also a SQL Execute step which you could use in a plan to manage these CRUD operations.

 

If the values do not match exactly, there are the fuzzy matching functions of the ONE Desktop available, such as distance, and levenshtein to help find matches in joined data.

 

I hope this helps!

View original
Catherine
Maxim Kim
2 people like this
  • Catherine
    Catherine
  • Maxim Kim
    Maxim Kim
Did this topic help you find an answer to your question?

1 reply

BrianF
Ataccamer
Forum|alt.badge.img+3
  • BrianFAtaccamer
  • Ataccamer
  • 5 replies
  • Answer
  • November 4, 2022

Hi Marnix,

If the keys/values match exactly, depending on use case, you can use an outer join in a Join step over your source and target tables, and set a column to flag rows with the required INSERT, UPDATE, DELETE operation, based on whether there are matching values in in_left.key and in_right.key or just one or the other.  You could then use Extract Filter and/or Filter steps to get lists of records for each operation.


Note that if you are referring to doing this on a database, there is also a SQL Execute step which you could use in a plan to manage these CRUD operations.

 

If the values do not match exactly, there are the fuzzy matching functions of the ONE Desktop available, such as distance, and levenshtein to help find matches in joined data.

 

I hope this helps!

Reply


Most Liked this week

Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings