depuplication based on other empty fields? | Community
Skip to main content
G
god_prophet
Level 6
November 3, 2023
Solved

depuplication based on other empty fields?

  • November 3, 2023
  • 1 reply
  • 811 views

Hi, I've duplicated records in recipients table.

The deduplication field would be: national_id, that is the one repeated. However, I just want to keep the record that have info in the other fields: first_name and last_name. 

How to remove the recoeds that are duplicated, based one national_id,  and  have the first_name and last_name fields empty?


+-------------+------------+-----------+-------------+
| national_id | first_name | last_name |             email |
+-------------+------------+-----------+-------------+
| 123            |            jose |        perez | jp@mail.com |
+-------------+------------+-----------+-------------+
| 123             |                  |                  |                        |
+-------------+------------+-----------+-------------+




When i select other, in the next screen, how to tell the deduplication activity to keep only the record where First Name is not empty. 


the Deduplucation activity will just separete in a temp table the records not duplicated from the duplicated ones, right?. ... but how do I actually remove the duplicated ones from recipients? The ones that doesn't have first_name or last_name



This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by ParthaSarathy

@god_prophet , try this,

Temporary schema >

Identification of duplicates: keep national_id alone.

Duplication method:

Doubles to keep: 1

Method: Using an expression

expression: 

Iif(Length(@firstName)+Length(@lastName)>0,1 ,0 )

select 'keep the records with largest value' radio button.

Now this will add your length of first name and last name. If both are empty, then the length will be 0, so the other national id with valid first name or last name will be in result.

1 reply

ParthaSarathy
Community Advisor
ParthaSarathyCommunity Advisor
Community Advisor
November 3, 2023

Hi @god_prophet ,

Select temporary schema in first tab.

In 'identification of duplicates' tab, along with national_id, add firstName and LastName as well.

~  ParthaSarathy S~  Click here to join ADOBE CAMPAIGN USER GROUP for Quarterly In-person | Hybrid | Virtual Meetups
G
god_prophetAuthor
Level 6
November 3, 2023

Hi @parthasarathy , 

I've added those in the identifcation of duplicated, but where do you tell Campaign to keep only the row where first and last name are not empty?


 




But this actually, would only separate the duplicated from the no duplicated records, right? 

How would I tell Campaign to remove the duplicated records, based on national_id, where first and last name are empty?

Edit 1: 

I'm getting this error from that config: 

03/11/2023 11:03:20 0 end ) FROM wkf68788481_15_1 W0 JOIN NmsRecipient R1 ON (R1.iRecipientId = W0.iId) WHERE ((R1.iRecipientId > 0 OR R1.iRecipientId < 0))' could not be executed.
03/11/2023 11:03:20 WDB-200001 SQL statement 'INSERT INTO wkf68788481_16_1 (iId,iInternalKeyField1,iInternalOrderNdx) SELECT W0.iId, W0.iId, Row_Number() OVER ( ORDER BY case when PARTITION BY R1.sCodinternocomputacional , R1.sFirstName , R1.sLastName IS NULL then 1 else
03/11/2023 11:03:20 PGS-220000 PostgreSQL error: ERROR: syntax error at or near "BY" LINE 1: ... Row_Number() OVER ( ORDER BY case when PARTITION BY R1.sCod... ^ .
03/11/2023 11:03:20 Executing query 'Result' (step 'Deduplication')

ParthaSarathy
Community Advisor
ParthaSarathyCommunity AdvisorAccepted solution
Community Advisor
November 3, 2023

@god_prophet , try this,

Temporary schema >

Identification of duplicates: keep national_id alone.

Duplication method:

Doubles to keep: 1

Method: Using an expression

expression: 

Iif(Length(@firstName)+Length(@lastName)>0,1 ,0 )

select 'keep the records with largest value' radio button.

Now this will add your length of first name and last name. If both are empty, then the length will be 0, so the other national id with valid first name or last name will be in result.

~  ParthaSarathy S~  Click here to join ADOBE CAMPAIGN USER GROUP for Quarterly In-person | Hybrid | Virtual Meetups
    Terms & ConditionsAccessibility statement

    Loading footer...