3 Replies Latest reply: Aug 12, 2016 6:39 AM by Sunny Talwar RSS

    Resident date?

    Paul Wonford

      Hi guys,

       

      I have data from three seperate sources

       

      Load

       

      PIN

      Application_Medium

      Application_date

       

      FROM

      [c:\Applications.xlsx]

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


      Load

       

      PIN

      Application_Medium

      Application_date

       

      FROM

      [c:\Checking.xlsx]

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


      Load

       

      PIN

      Application_Medium

      Application_date

       

      FROM

      [c:\Validation.xlsx]

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


      I'm looking to produce an autonumber using the Autonumber field. If it was just one table i'd simply add autonumber to the load script of that table but I cant do this because it's three tables. My question is - once the data from the three tables has been loaded in; how can i then add an autonumber. Do i use a resident function?


      What i'd like to see in the end is this


       

      DateFileAuto number
      01/01/2016Applications1
      01/01/2016Checking1
      01/01/2016Validation1
      01/02/2016Applications2
      01/02/2016Checking2
      01/02/2016Validation2
      01/03/2016Checking3
      01/04/2016Applications4
      01/04/2016Validation4



        • Re: Resident date?
          Stefan Wühl

          Maybe you can use a RESIDENT LOAD, but maybe you don't need it. Based on what criteria do you want to assign the auto number to your records?

           

          Maybe use just a

           

          LOAD

               Recno() as Autonumber,

               ...

           

          in your three table loads.

           

          edit:

          If your autonumber is based on date, a

           

          LOAD

               Autonumber(Application_Date) as Autonumber,

               ...

           

          should work, the autonumber()  is done across multiple table loads as well.

            • Re: Resident date?
              Paul Wonford

              Im not explaining myself well.

               

              I have dates in two tables.

               

              Table1:

              01/01/2016

              01/03/2016

               

              Table2:

              01/02/2016

              I want to combine those dates and autonumber from earliest to latest so I have

               

              01/01/2016 - date 1

              01/02/2016 - date 2

              01/03/2016 - date 3