How to get the latest entry if two or more records found | Community
Skip to main content
G
gunjans6748888
Level 3
April 24, 2018
Solved

How to get the latest entry if two or more records found

  • April 24, 2018
  • 4 replies
  • 3912 views

Hi Team,

I am trying to extract App subscription data and want to only extract latest entry if more than one entry found with the same userKey

So, the filter needs to be done on 'registration date' and if entry1 and entry 2 are same, we need to take the latest entry among them.

Thank you.

    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 Adhiyan

    Hi Gunjan,

    I can think of a couple of solutions here.

    1>.Create a query for pulling all data which has condition userkey is not empty and then deduplicate based on the user key. By default , the deduplication is run on the lastModified field and will only keep the latest records.

    2>. If this is for targeting purposes , you can also use an Incremental query which only shows you changed , modified or latest records which were previously not processed

    Regards,

    Adhiyan

    4 replies

    A
    AbdulZa
    Level 3
    April 24, 2018

    Depending on the DB used the syntax maybe different. You simply need to apply some sort of ranking (rank using subscriber key and order by registration date descending. It is a datetime format so the possibility to pick up records or more for the same subscriber is minimum.

    For example, if using SQL Server, you can use the partition by command to do that.

    J
    johnf48867289
    Level 2
    April 25, 2018

    Thank you for this response. I was interested in how others approach this, too.

    Kapil_Sharma_
    Adobe Employee
    Kapil_Sharma_Adobe Employee
    Adobe Employee
    April 25, 2018

    Hi,

    You can sort the records on the basis of Registration Date in Descending Order and then you can select the First Record.
    This can be achieved either by the JS Activity or you can achieve this by Enrichment itself.

    Adhiyan
    Adobe Employee
    AdhiyanAdobe EmployeeAccepted solution
    Adobe Employee
    April 25, 2018

    Hi Gunjan,

    I can think of a couple of solutions here.

    1>.Create a query for pulling all data which has condition userkey is not empty and then deduplicate based on the user key. By default , the deduplication is run on the lastModified field and will only keep the latest records.

    2>. If this is for targeting purposes , you can also use an Incremental query which only shows you changed , modified or latest records which were previously not processed

    Regards,

    Adhiyan

    Terms & ConditionsAccessibility statement

    Loading footer...