topic Re: Straight Table filtering in QlikView

Straight Table filtering

Sun, 20 Dec 2015 04:31:45 GMT

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

Re: Straight Table filtering

swuehl — Sun, 20 Dec 2015 08:11:55 GMT

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;

Re: Straight Table filtering

Mon, 21 Dec 2015 02:52:32 GMT

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;

Re: Straight Table filtering

jagan — Mon, 21 Dec 2015 03:53:05 GMT

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.