0

I am trying to deduplicate my entry data extension based on the completeness of field values. For Instance, i have the a data extension with same email address, the record that has maximum field values filled in, that should be selected.

For instance, if I have three records and I have 4 columns, then the record which has values for all the columns or maximum columns should be selected.

  • Hi, Swati, what methods have you tried until now for solving your issue? How many columns do you need to consider? What do you do in a situation where you have 2 or more rows with the exact same number of non-empty columns? Seeing what attempts didn't work and knowing more details about the situation might help someone to help you to find a solution. – Rafał Wolsztyniak Dec 11 '19 at 14:55
2

I think you would have to separate this into 2 queries. One to a staging table and then another to overwrite the final file.

Query 1:

SELECT EmailAddress,
       Field1,
       Field2,
       Field3,
       Field4,
       CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL AND Field3 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL AND Field4 != '' THEN 1 ELSE 0 END AS Field5
FROM TableA

This will fill in the total number of not null and not empty columns in your DE. You would then use a second query to pull only those with the highest 'Field5' value per email address like so:

SELECT t.EmailAddress,
t.Field1,
t.Field2,
t.Field3,
t.Field4
FROM [Staging_DE] t
INNER JOIN (
    SELECT a.EmailAddress,
    MAX(a.Field5) as Field5
    FROM [Staging_DE] a
    GROUP BY EmailAddress
) mx
ON mx.EmailAddress = t.EmailAddress AND mx.Field5 = t.Field5

Where Staging_DE is the target of your Query 1.

You can technically have this all be inside a single query and just replace Staging_DE with a subquery using Query 1.

E.g.

SELECT t.EmailAddress,
t.Field1,
t.Field2,
t.Field3,
t.Field4
FROM (
       SELECT EmailAddress,
       Field1,
       Field2,
       Field3,
       Field4,
       CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL AND Field3 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL AND Field4 != '' THEN 1 ELSE 0 END AS Field5
       FROM TableA
) t
INNER JOIN (
    SELECT a.EmailAddress,
    MAX(a.Field5) as Field5
    FROM (
       SELECT EmailAddress,
       Field1,
       Field2,
       Field3,
       Field4,
       CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL AND Field3 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL AND Field4 != '' THEN 1 ELSE 0 END AS Field5
       FROM TableA
    ) a
    GROUP BY EmailAddress
) mx
ON mx.EmailAddress = t.EmailAddress AND mx.Field5 = t.Field5
| improve this answer | |
  • 1
    Thanks a lot! It worked :) – Swati Mishra Dec 12 '19 at 12:15
0

Could you use the LEN() function to determine the length of the max field value. Something like this?

Select * From [Table Name] Where LEN(Field Name) = 250 (or whatever the max character length is)

| improve this answer | |
  • Thanks.. But what I need is : For instance, if I have three records and I have 4 columns, then the record which has values for all the columns or maximum columns should be selected. – Swati Mishra Dec 11 '19 at 14:27
  • Ah, I misunderstood your question. I understand now. Could you use something like this? WHERE [field name] <> '' AND [field name] IS NOT NULL for each of the 4 columns? – Clint Woodruff Dec 11 '19 at 14:32
  • i could .. But the fields are not required, there could be case where not all 4 fields will be filled out, it may be only three, in that case none of the records will be selected. – Swati Mishra Dec 11 '19 at 14:38
0

I have only tried this in SQL Management Studio, but should work in Marketing Cloud too. It should be enough to get you going.

SELECT ContactKey, PopulatedColumns
    FROM (
    SELECT 
        ContactKey,
        (CASE WHEN COALESCE(Column1, '') != '' THEN 1 ELSE 0 END +
        CASE WHEN COALESCE(Column2, '') != '' THEN 1 ELSE 0 END +
        CASE WHEN COALESCE(Column3, '') != '' THEN 1 ELSE 0 END +
        CASE WHEN COALESCE(Column4, '') != '' THEN 1 ELSE 0 END) AS PopulatedColumns 
        FROM Table
    ) t
ORDER BY PopulatedColumns DESC
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.