0 Replies Latest reply: Sep 5, 2011 8:47 AM by Donald Posthuma RSS

    Add unique identifier during load

    Donald Posthuma

      I'm trying to add an unique identifier to each record during load. Since I'm combining various data source I need a variable to keep track of this identifier. Below the script I have without the identifier. So I need a way to update QVId during the load. Again your help is very much appreciated!

       

      COMPANIES:                                                                                                                                                                                                                                                                                                                                          ///Create table containg file names for loop, companies and qualifiers
      LOAD * INLINE

         [COMPANY
          FUEL
          GAS]
      ;

       

      DATABASES:
      LOAD * INLINE
                [DATABASE
                 CONFIRMED
                 HISTORY
                 ACTIVITY]
      ;

                           
      LET QVId = 0;

       

      LET n = NOOFROWS('COMPANIES');
      FOR i = 1 TO n
      LET vC = FIELDVALUE('COMPANY',i);

      $(vC)ACTIVITY:

                LET m = NOOFROWS('DATABASES');
                FOR j = 1 TO m
                LET vDB = FIELDVALUE('DATABASE',j);

       


      LOAD
        UNIQUE IDENTIFIER AS QVId,
                *
      FROM
      $(vC)_DC_$(vDB).QVD (QVD)
      WHERE NOT EXISTS('KEYCoHiAc', Source & TruckId & ShiftDate & ShiftNb & TripSequenceNb & SequenceNb & SubSequenceNb & Activity & SiteId & StorageId & MaterialId);

       


      LET QvId = PEEK('QVId');

       

      NEXT

       

      STORE $(vC)ACTIVITY INTO $(vC)ACTIVITY.QVD (QVD);
      DROP TABLE $(vC)ACTIVITY;

       

      NEXT

       

      DROP TABLES COMPANIES, DATABASES;