Straight Table filtering

[#1]

Hi,

In the Straight Table below, I only want to load those PO_NO where the Status is 200 and on the min date. (They are same PO with same Status 200 but different date, due to change in Status back and fore)

e.g for PO_NO 123, I only wants to load the PO where the status is 200 and the first Date is 12/12/2013, igore the same PO dated 12/13/2015, 12/14/15. Same for the rest of the PO.

I tried min(date(PO_NO) as MinPoDate but failed.

Appreciate anyone could help. Thank you.

[TrackTraces]:

Load   

   PO_NO, StatusDate, Status,

              FROM [Track.qvd] (qvd) ;

PO_NO	StatusDate	Status
123	12/12/2015	200
123	12/13/2015	200
123	12/14/2015	300
124	12/12/2015	200
124	12/13/2015	200
124	12/14/2015	400
125	12/12/2015	200
125	12/13/2015	200
125	12/14/2015	450

[swuehl]

Maybe like this:

SET DateFormat = 'MM/DD/YYYY';

Filter:

Load  

   PO_NO & '-' & num(Min(StatusDate)) as Filter

FROM [Track.qvd] (qvd)

WHERE Status = 200

GROUP BY PO_NO;

[TrackTraces]:

Load  

   PO_NO, StatusDate, Status,

FROM [Track.qvd] (qvd)

WHERE EXISTS(Filter, PO_NO & '-' & num(StatusDate)) and Status = 200;

DROP FIELD Filter;

[#3]

Hi ,

The statement should be RESIDENT [Shipment], Sorry was my mistake , it was not taken from QVD but from a Table [Shipment]. After I re-load, I have "$Syn 1 error" for PO_NO, StatusDate, Status as these 3 fields also appear in [Shipment] Table, how to resolve this. Appreciate your help. Thank you.

Filter:

Load  

   PO_NO & '-' & num(Min(StatusDate)) as Filter

RESIDENT [Shipment]

WHERE Status = 200

GROUP BY PO_NO;

[TrackTraces]:

Load  

   PO_NO, StatusDate, Status,

RESIDENT [Shipment]

WHERE EXISTS(Filter, PO_NO & '-' & num(StatusDate)) and Status = 200;

DROP FIELD Filter;

[jagan · Accepted solution]

HI,

Try like this

[TrackTraces]:

Load  

   PO_NO, Min(StatusDate) AS StatusDate, Status,

              FROM [Track.qvd] (qvd)

WHERE Status = 200

GROUP BY PO_NO, Status;

Regards,

Jagan.