topic Re: Removing Duplicates based on Date and Time Stamp in QlikView

Removing Duplicates based on Date and Time Stamp

zach_paz — Thu, 21 Nov 2013 18:25:45 GMT

I am trying to load only the most recent records based on date and time stamp.

I have one QVD with the following records:

lead_id

status_name_update

status_date_update

There are multiple status names with a date and time stamp for each lead_id. Example:

lead_id status_name_update status_date_update

1234 contacted 41456.942928241

1234 converted 41456.078564815

I want only the latest record to be loaded:

lead_id status_name_update status_date_update

1234 converted 41456.942928241

This is the table I am using:

LOAD lead_id,

status_name_update,

status_date_update

FROM

(qvd);

I have tried multiple solutions from other QV Community posts but cannot seem to get anything working properly. Please help!

swuehl — Thu, 21 Nov 2013 18:31:10 GMT

Maybe like this:

INPUT:

LOAD lead_id,

status_name_update,

status_date_update

FROM

(qvd);

CURRENT:

LOAD lead_id,

FirstSortedValue(status_name_update, -status_date_update) as LastStatus,

max(status_date_update) as LastUpdate

RESIDENT INPUT GROUP BY lead_id;

DROP TABLE INPUT;

zach_paz — Thu, 21 Nov 2013 18:38:06 GMT

It works!

Thank you!!