Question 37005 in Jquery Question posted in
The official Jquery Api Documentation can be found here.

Remove duplicate rows based on 3 columns – Jquery

The below code returns a table with values from a web list. Some values are duplicates. I need to remove all duplicates where "User_x0020_Name", "Previous_Total_Most_Likely_Forec", "Submitted_Total_Most_Likely_Fore" are the same and only keep the latest record (maximum of "Created") for a duplicates set.

function loadAuditTrailFinancials() {
    var auditTrailURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Audit_Trail_Financial_Data')/items?$select=Author/Title,Previous_Total_Most_Likely_Forec,Submitted_Total_Most_Likely_Fore,Forecast_Approval_Reason,Title,Created,Workflow_Stage_Name,WorkflowStageShort,Source,Event&$filter=Title eq '" + PDP_projUid + "'&$orderby=Created desc&$expand=Author/ID";
    console.log("loadAuditTrailFinancials:" + auditTrailURL);
    $.ajax({
            url: auditTrailURL,
            method: "GET",
            headers: {"Accept": "application/json; odata=verbose"},
            success:    function (data) {
                            var items = data.d.results;
                            for (var i = 0; i < items.length; i++) {
                                var creation_date = items[i].Created;                           
                                var current_user = items[i].User_x0020_Name;
                                console.log(items[i]);
                                $('#AuditTrailTable_Financial').append('<tr class="audit_content">' +
                                    '<td align=center>&nbsp;' + format_date(creation_date) + '&nbsp;</td>' +     
                                    '<td align=center>&nbsp;' + items[i].WorkflowStageShort+ '&nbsp;</td>' +
                                    '<td align=center>&nbsp;' + items[i].Author.Title + '&nbsp;</td>' +
                                    '<td align=center>&nbsp;' + items[i].Source + '&nbsp;</td>' +
                                    '<td align=center>&nbsp;' + items[i].Previous_Total_Most_Likely_Forec + '&nbsp;</td>' +
                                    '<td align=center>&nbsp;' + items[i].Submitted_Total_Most_Likely_Fore + '&nbsp;</td>' +
                                '</tr>');                               
                            }
                            $('.audit_content').hide();
                            console.log(data);
                        },
            error: function (data) { alert("Some error occurred in getting Audit Trail")}
    });

}
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you everyone for your replies.

    I have sorted the table in descending order and used the below code to solve the issue:

    function (data) {
  var items = data.d.results;
  var current_user = "";
  var current_Previous_Total = 0;
  var current_Submitted_Total = 0;

  for (var i = 0; i < items.length; i++) {
    var creation_date = items[i].Created;                           
    if (current_user != items[i].User_x0020_Name || current_Previous_Total != items[i].Previous_Total_Most_Likely_Forec || current_Submitted_Total != items[i].Submitted_Total_Most_Likely_Fore) {
      current_user = items[i].User_x0020_Name;
      current_Previous_Total = items[i].Previous_Total_Most_Likely_Forec;
      current_Submitted_Total = items[i].Submitted_Total_Most_Likely_Fore;
       $('#AuditTrailTable_Financial').append('<tr class="audit_content">' +
        '<td id="tdDate" align=center>&nbsp;' + format_date(creation_date) + '&nbsp;</td>' +     
        '<td id="tdStage" align=center>&nbsp;' + items[i].WorkflowStageShort+ '&nbsp;</td>' +
        '<td id="tdUser" align=center>&nbsp;' + items[i].Author.Title + '&nbsp;</td>' +
        '<td id="tdSource" align=center>&nbsp;' + items[i].Source + '&nbsp;</td>' +
        '<td id="tdPrevious" align=center>&nbsp;' + items[i].Previous_Total_Most_Likely_Forec + '&nbsp;</td>' +
        '<td id="tdSubmitted" align=center>&nbsp;' + items[i].Submitted_Total_Most_Likely_Fore + '&nbsp;</td>' +
      '</tr>');
    }                               
  }
  $('.audit_content').hide();
  console.log(data);
},

  2. 0

    Maybe you can do this using a hash map to store index location of the unique triplet in order to replace it when we find a newer one.

    // This will be our final result array, with no duplicates.
const result = []
// Here we will store the result index location of a stored item.
const latest_map = new Map()

// build a unique key for the triplet combo.
function buildKey(item) {
    const separator = "__"
    return item.User_x0020_Name + separator + item.Previous_Total_Most_Likely_Forec + separator + item.Submitted_Total_Most_Likely_Fore
}

// check if item exist, replace
function deduplicateAndKeepLatest(item) {
    const key = buildKey(item);
    if (latest_map.has(key)) {
        result[latest_map.get(key)] = item; // replace old item with latest.
    } else {
        result.push(item);
        latest_map.set(key, result.length-1) // first time seeing this item, save the index it was stored at.
    }
}

// make sure items is in the correct sort order, since you want to keep the latest.
items.forEach((item) => {
    deduplicateAndKeepLatest(item);
}

return result; // should contain unique triplet, and only latest one.
    Login or Signup to reply.
  3. 0 
    //apply a filter on data.d.result array
var items = data.d.results.filter(function (el, i, arr) {
    //if index match then this element is keeped
    //arr is the array on which filter was called (is data.d.result)
    return i === arr.findIndex(function (obj) {
        //need to satisfies this conditions
        return obj.User_x0020_Name === el.User_x0020_Name && 
obj.Previous_Total_Most_Likely_Forec === el.Previous_Total_Most_Likely_Forec && 
obj.Submitted_Total_Most_Likely_Fore === el.Submitted_Total_Most_Likely_Fore && 
obj.Created >= el.Created;
        })
    });

    reference:
    Array.prototype.filter()
    Array.prototype.findIndex()

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search