Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading data partially based on unique records and field values

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!

1 Solution

Accepted Solutions
tanelry
Contributor II

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

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 Replies
tanelry
Contributor II

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

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

Not applicable

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

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
Contributor II

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

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.

Community Browser