0

I have a data extension that has duplicate email addresses with each duplicate having a different rank associated with it. I need to dedupe the email addresses but keep the different ranks in added fields.

So I currently have:

Email_address, Rank
Email1, rank1
Email1, rank2
Email1, rank3
Email2, rank1
Email2, rank2

And what I need is:

Email_address, Rank1, Rank2, Rank3
Email1, rank1, rank2, rank3
Email2, rank1, rank2

I've tried using the CASE function in an SOQL query but it's not returning the outcome I was looking for. Is there a way of getting the data how I need it using querys?

1

I think you could achieve this with a self join. Something like...

SELECT Email_Address, 
t1.Rank as Rank1, 
t2.Rank as Rank2, 
t3.Rank as Rank3
FROM table t1 
LEFT OUTER JOIN table t2 on t1.EmailAddress = t2.EmailAddress and t2.Rank = 'rank2'
LEFT OUTER JOIN table t3 on t1.EmailAddress = t3.EmailAddress and t3.Rank = 'rank3'
WHERE 1=1
AND t1.rank = 'rank1'

(I always throw 1=1 so each clause thereafter can start with AND)

| improve this answer | |
  • The only issue you might run into with this (and it may not be a possibility in your use case) is if the email address does not have a 'rank1' value in Rank, it will not appear in your results. – Gortonington Nov 15 '18 at 13:47
0

Is it possible to keep all the ranks in 1 single field? If yes, then you can use STUFF with FOR XML PATH functions in SQL. You can check out this link

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