3 Replies Latest reply: Aug 26, 2011 3:42 AM by Tanel Rüütli RSS

    Loading data partially based on unique records and field values

    Donald Posthuma

      I'm trying to load data from a QVD file and limiting the data based on field values. This contains a WHERE statement and a PEEK statement. This is my code:

       

       

      T_TRIPSTOP:
      LOAD 
          
      TruckId & ShiftDate & ShiftNb& TripSequenceNb & SequenceNb & APPLYMAP('MAPSITES', SiteId) & Source AS TripStopID,
      FROM
      tTrips.QVD
      (
      qvd)
      WHERE ((Source = 'DC' OR Source = 'DP') AND ((Activity = 'Load' OR Activity = 'Delivery' OR Activity = 'StartInventory')));
        
      QUALIFY *;
      UNQUALIFY #TripStopID
      TRIPSTOP: 
      LOAD
          
      TripStopID AS #TripStopID,
          
      TripStopID,
      RESIDENT
                T_TRIPSTOP

      WHERE TripStopdID <> PEEK('TripStopID');



      Right now this doens't work correct since PEEK looks at the previous record and the records are not sorted. However if I use the ORDER BY function within the first LOAD statement I get the message 'Garbage after statement'. I could put another LOAD statement in between but this seems a lot of trouble for the operation I want. Is there a smoother way to achieve the desired table: Restriction in the WHERE clause and no duplicate records from TripStopID? BTW I can't use Distinct since T_TRIPSTOP contains a lot more fields which are not all equal. Thanks in advance!

        • Re: Loading data partially based on unique records and field values
          Tanel Rüütli

          As I understand, you want to load just the first occurence of every TripStopID.

          Have you tried

           

          T_TRIPSTOP:
          LOAD
          TruckId & ShiftDate & ShiftNb& TripSequenceNb & SequenceNb & APPLYMAP('MAPSITES', SiteId) & Source AS TripStopID,
          Otherfields
          FROM tTrips.QVD (qvd)
          WHERE match(Source, 'DC', 'DP') AND match(Activity, 'Load', 'Delivery', 'StartInventory')
          AND NOT Exists('TripStopID', TruckId & ShiftDate & ShiftNb& TripSequenceNb & SequenceNb & APPLYMAP('MAPSITES', SiteId) & Source );
          
          

           

          This would work in QV9 and QV10SR3.

          Earlier SRs of QV10 had buggy exists() function (see this thread).

            • Re: Loading data partially based on unique records and field values
              Donald Posthuma

              Hey Tanel

               

              This works! However I'm not sure if I can incorporate it. For each of the TRIPSTOPS I need to Aggregate various Quantities to get the quantity for this tripstop. I use a Group by combined with an aggregation to get the desired result and Join it with the Unique records. The Group By requires a resident load which still leaves me with a temporary table. Is there a way to Aggregate over the tripstop within the initial load?

                • Re: Loading data partially based on unique records and field values
                  Tanel Rüütli

                  Try to use preceding load, something like this:

                   

                  T_TRIPSTOP:
                  LOAD // second load: based on records returned by the preceding load statement (below)
                  TripStopID,
                  sum(Otherfield1) as Qty1,
                  sum(Otherfield2) as Qty2
                  group by TripStopID;
                  LOAD // initial load from QVD: create field TripStopID and filter records by Source and Activity
                  TruckId & ShiftDate & ShiftNb& TripSequenceNb & SequenceNb & APPLYMAP('MAPSITES', SiteId) & Source AS TripStopID,
                  Otherfield1,
                  Otherfield2
                  FROM tTrips.QVD (qvd)
                  WHERE match(Source, 'DC', 'DP') AND match(Activity, 'Load', 'Delivery', 'StartInventory');
                  
                  

                   

                  I sometimes stack 3-4 load statements like this.