Loading data partially based on unique records and field values

[#1]

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!

[tanelry · Accepted solution]

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).

[#3]

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?

[tanelry]

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.