Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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).
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).
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?
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.