8 Replies Latest reply: Sep 21, 2016 4:16 PM by Sunny Talwar RSS

    De-duplicating Simple Data Table in Load Script

    Karen Mujeyi

      Hello QV,

       

      I have a very simple (but large) Staff table that gets updated more frequently than I would like it to be.  Because of this, I am trying to put in into QV to help automate some reports.

       

      The issue: for most of my reports, I only care about my Employee ID Number (EIN) and the Manager that is associated with them for this duplicate identificaiton.  My list though includes the various Name variations we have on file for any given employee.  I want to recreate this list, but remove any duplicates.  Here is some basic sample.

       

         

      EINNameLoginManager
      1234A SmithAS34Manager1
      1234Al SmithAS34Manager1
      5678J CoopJC78Manager2
      9123L ParkLP23Manager3

       

      I thought I could create a "mapping" script to de-dup, but apparently I am still not good enough at this as none of the versions I have tried actually remove the duplicates.

       

      Here is what I thought would work but does not as I still have duplicates:

       

       

      Staff_Mapping:
      Mapping
      LOAD Distinct
      EIN
      ,
      Manager
      FROM
      [V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\Daily Productivity Report\MasterUserDecodeList - 9.9.16.xlsx]
      (
      ooxml, embedded labels, table is UpdatedUserList);

      Staff_List:
      LOAD
      Login,
      UserName,
      Title,
      Manager,,
      EIN,

      EIN as [Nurse Decode #],
      ApplyMap('Staff_Mapping', EIN) as UniqueFlag
      FROM
      [V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\Daily Productivity Report\MasterUserDecodeList - 9.9.16.xlsx]
      (
      ooxml, embedded labels, table is UpdatedUserList);

       

       

      Thank you in advance for your help in getting these duplicates removed!