8 Replies Latest reply: Apr 18, 2016 10:02 AM by Sunny Talwar RSS

    Filter rows

    gidon david

      Hi guys

      enclosed is a file where each part number has one or more lines

      form this list I want to keep only line

      with later DOCDATE date for each PARTNAME

       

      FOR EXample

         

      PARTNAMEfileNoDOCDATENet Price
      615732-B21107/03/2016134.54
      615732-B21115/04/2016133.00
      647594-B21107/03/2016276.00
      647594-B21115/04/2016230.00

       

       

      from this file I want to keep  the second and the forth lines as the date is later for each PARTNAME

      615732-B21 , 1 , 15/04/2016 ,133.00

      647594-B21 ,1,15/04/2015,230.00

       

      enclosed the file

      thanks

      gidon

        • Re: Filter rows
          Stefan Wühl

          There are multiple options, maybe like

           

          SET DateFormat ='DD/MM/YYYY';
          
          INPUT:
          LOAD PARTNAME, 
               fileNo, 
               DOCDATE, 
               [Net Price]
          FROM
          [E:\Users\Stefan\Downloads\PART-SORT.xls]
          (biff, embedded labels, table is Sheet1$);
          
          LEFT JOIN (INPUT)
          LOAD PARTNAME,
            Max(DOCDATE) as DOCDATE,
            1 as Flag
          RESIDENT INPUT
          GROUP BY PARTNAME;
          
          RESULT:
          NoConcatenate 
          LOAD PARTNAME, 
               fileNo, 
               DOCDATE, 
               [Net Price]
          Resident INPUT
          WHERE Flag;
          
          DROP TABLE INPUT;     
          

           

           

          or like

           

          SET DateFormat ='DD/MM/YYYY';
          
          INPUT:
          LOAD PARTNAME, 
               fileNo, 
               DOCDATE, 
               [Net Price]
          FROM
          [E:\Users\Stefan\Downloads\PART-SORT.xls]
          (biff, embedded labels, table is Sheet1$);
          
          LOAD PARTNAME,
            Max(DOCDATE) as DOCDATE,
            FirstSortedValue(fileNo,-DOCDATE) as FileNo,
            FirstSortedValue( [Net Price],-DOCDATE) as  [Net Price]
          RESIDENT INPUT
          GROUP BY PARTNAME;
          
          
          DROP TABLE INPUT;