Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

using MAX date

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';

1 Solution

Accepted Solutions
sunny_talwar

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';

View solution in original post

2 Replies
sunny_talwar

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';

Anonymous
Not applicable
Author

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

Load

Submitted date

From LastSubmitted

Order By Submitted desc

Left Join

    Submitted,
    RequestType,

    FacID,

    PatID,

    Request,

    Response,

    ts

FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';