2 Replies Latest reply: Jan 11, 2015 6:14 PM by Gabriel Oluwaseye RSS

    Exclude rows in load statement

      Hello,

       

      I'm loading data based on phone numbers from a file to analyse. The purpose is basically sorting the data(number of rows) down to dates.

       

      That isn't a problem but what I want to do first, preferably in the load, is to exclude the first 10 rows imported for each distinct phone number.

       

      Any ideas?

       

      Regards, Jon

        • Re: Exclude rows in load statement
          Marco Wedel

          maybe

           

          LOAD *

          FROM source

          Where Autonumber(RecNo(),[phone number]) > 10;

          • Re: Exclude rows in load statement
            Gabriel Oluwaseye

            Hi,

             

            May be try something like this

             

            //Just the telephone number with distinct. This will give the number of DISTINCT tel number

            Final_Table:

            LOAD

                Distinct

                [Telephone Number]           

            FROM

            [Telephone Number Data.xlsx]

            (ooxml, embedded labels, table is Worksheet)

            WHERE AutoNumber(RecNo())>10;

             

            Then

            //Left Join the rest of the fields with the 1st load

            LEFT JOIN(Final_Table)

            LOAD

                [Telephone Number]

                ,[Customer Name],

                Country,

                Date

            FROM

            [Telephone Number Data.xlsx]

            (ooxml, embedded labels, table is Worksheet)

            WHERE Exists([Telephone Number])

            ;

             

            Hope this helps