1 Reply Latest reply: Jun 10, 2014 7:02 AM by Tresesco B Branched to a new discussion. RSS

    Last 5 rows of data

    Rahul Lakhina

      Hi,

       

      I have a back end table that looks something like the file attached.

       

      For each row and each Key I want to be able to look at previous 4 rows and count the number of Advisor lines and calculate the field 'Last5Count' as in the excel attached, could someone please suggest a solution.

       

      Please note i have many more fields and have 800k rows worth of data.

       

      Regards

       

      Rahul

        • Re: Last 5 rows of data
          Tresesco B

          PFA

           

          Load
          *,
          SubStringCount(Peek(Identifer_by)&Peek(Identifer_by,-2)&Peek(Identifer_by,-3)&Peek(Identifer_by,-4)&Peek(Identifer_by,-5),'Advisor') as Last5Count;

          LOAD Key,
              LineCount,
              Post_Diff_NonSystem,
              Identifer_by,
              Advisor_Counter,
              System_Counter,
              Customer_Counter,
              NonSystem_Counter
              // Last5Count
          FROM
          [C:\Users\...\Desktop\sample_date_v1.xlsx]
          (ooxml, embedded labels, table is Sheet1);