1 Reply Latest reply: Aug 11, 2016 5:37 PM by Sunny Talwar RSS

    Date number

    Paul Wonford

      Hi guys,

       

      I have a file with a list of dates

       

      Load

       

      PIN

      Application_Medium

      Application_date

       

      FROM

      [c:\Applications.xlsx]

      (ooxml, embedded labels, table is [Results]);

       

      Is there an autonumber function i can use to assign a sequential number to each date starting from lowest to highest? So for example

       

      DateDate #
      01/01/20161
      01/02/20162
      01/03/20163
      01/04/20164
        • Re: Date number
          Sunny Talwar

          Sure, you can try

           

          AutoNumber(Application_date) as Date#

           

          but you will have to make sure that the table is sorted in the ascending order of Application_date to give the correct sort order. Sorting (using Order By) can only be done in Resident load.


          Something along these lines:


          Table:

          LOAD PIN

          Application_Medium

          Application_date

          FROM

          [c:\Applications.xlsx]

          (ooxml, embedded labels, table is [Results]);


          FinalTable:

          LOAD *,

                    AutoNumber(Application_date) as Date#

          Resident Table

          Order By Application_date;


          DROP Table Table;