"Deduplicate by Lookup" like "Select by lookup"

Feature requests
1

Hi,
it would be very nice to have the deduplication Action a bit more dynamically.
I determine the database keys (UniqueKey) using PLSQL function (have it in one table).
Then I would like to deduplicate the data of a table accordingly so as not to get any key violations during Database INSERT.

It would be like “Deduplicate by lookup”

Sure, you could also do that with various Merge , Append Table …
To improve existing Action would be much nicer :slight_smile:

image
image1270×846 228 KB

is it planned?

regards,

Adrian

2

Hi Adrian,

I’m not sure I understand how this “Deduplicate by lookup” action would work. If you need to deduplicate only specific IDs, then you can do it in a derived table + “Keep mis/matching” action + “Deduplicate” action.

If you just need to remove particular IDs because they already exist in the target DB tables, then the “Keep mis/matching” action would do it.

3

Hi Dmitry, thanks for feedback. I thought I had described the request well by illustration above, but ok… here an example.
The goal is to deduplicate duplicates in the data related to the database keys before exporting to the database.
Many XML messages/files are processed simultaneously (sorted by “Date Modified”). The “latest” Data regarding DatabaseKey wins . The example contains 2 messages/files.
In this case the key consists of the columns in db-key.dset
DeduplicateKeys.morph (5.1 KB) DB-Key.DSET (209 Bytes) XML-Data.DSET (69.1 KB)

4

Oh, I didn’t realize those were column names in the list. Now I see your point. Thank you for the clarification.

1 Like
5

Hi Dmitry, unfortunately you didn’t reply.
Would it be possible that you implement this in one of the next Versions?

The lookup function would also be very helpful for other actions.
For example “Convert Data Type by Lookup”:
In DB information schema we read the types of Database columns.
The conversion to Date/Time is always an issue.
“Convert by Look Up” date time columns could generically change the data type for the affected columns.

regards,

Adrian

6

Hi Adrian,

the functionality you are suggesting can already be achieved using existing actions (albeit in a few steps rather than one). Therefore, I see it as “nice to have” rather than “must have” in terms of added value. So at this point the suggestion is a low priority for the product development pipeline.

7

Hi Dmitry, thanks for feedback…ok, thanks… How would you solve this in my example above in the fastest / most uncomplicated way? I suppose with Pivot … Unpivot?
Regards, Adrian

8

I have to admit it turned out a bit more complex than I expected. Here are two variants:

Variant 1 uses the eval() function.
Variant 2 uses Pivot.

Demo.zip (244.7 KB)

2 Likes
9

Thank you very much for the two proposed solutions!
Yes… it’s complex AND happens very often :upside_down_face:
Perhaps the complexity brings the requirement a little forward in TimeLine :wink: because a quick solution 'll be verry helpfull. Thanks again for this great support. :+1: