topic using MAX date in App Development

using MAX date

Thu, 07 Sep 2017 19:07:58 GMT

ok this is what I am trying to do.

in the pic below it is displaying an audit log that and the response from that transaction. I was hoping to only pull in the latest response per PatID.

How can I do it to only display the most recent submitted date?

Ideally it possible in the load?

I will also have to add other tables at a later point if this is a factor?

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD SubmitGUID,
    Submitted,
    RequestType,
    FacID,
    PatID,
    Request,
    Response,
    ts;
SQL SELECT SubmitGUID,
    Submitted,
    RequestType,
    FacID,
    PatID,
    Request,
    Response,
    ts
FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';

Re: using MAX date

sunny_talwar — Thu, 07 Sep 2017 19:13:47 GMT

May be this

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD FirstSortedValue(SubmitGUID, - Submitted) as SubmitGUID,
Max(Submitted) as Submitted,

FirstSortedValue(RequestType, - Submitted) as SubmitGUID,

FirstSortedValue(FacID, - Submitted) as SubmitGUID,

PatID,

FirstSortedValue(Request, - Submitted) as SubmitGUID,

FirstSortedValue(Response, - Submitted) as SubmitGUID,

FirstSortedValue(ts, - Submitted) as SubmitGUID

Group By PatID;

SQL SELECT SubmitGUID,
    Submitted,
    RequestType,
    FacID,
    PatID,
    Request,
    Response,
    ts
FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';

Re: using MAX date

Anonymous — Thu, 07 Sep 2017 19:17:24 GMT

So you can do Max(SubmittedDate)

and Group by everything that is not the submitted date after the from.

Or you can do

LastSubmitted:

Load

Submitted date

From FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';

TableName

First 1

Load

Submitted date

From LastSubmitted

Order By Submitted desc

Left Join

Submitted,
RequestType,

FacID,

PatID,

Request,

Response,

FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';