Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

DateTime based on lowest SeqID

Good day, all.  I'm having a script challenge this morning.

NOTE:  Edited to include entire table.

My script looks like this:

EdmPatientStatusEventHistory:
LOAD

SourceID,
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
HistoryUser,
StartRW,
RowUpdateDateTime,
HistoryUserType,
Comment,
StartDateTime,
ActualFileDateTime;
SQL
SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory;

What I need is for each VisitID, the DateTime associated with the lowest value in SeqID, only where EventID = 'IN ROOM'.

Any ideas?  I'm sure this is a simple fix for those who are better at this than I am.

Message was edited by: Sam Chambers

1 Solution

Accepted Solutions
maxgro
Not applicable

Re: DateTime based on lowest SeqID

EdmPatientStatusEventHistory:
LOAD

SourceID,
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
HistoryUser,
StartRW,
RowUpdateDateTime,
HistoryUserType,
Comment,
StartDateTime,
ActualFileDateTime;
SQL
SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory;


left join (EdmPatientStatusEventHistory)

load VisitID, StartDateTime as MinStartDateTime           // StartDateTime or DateTime or ....

resident EdmPatientStatusEventHistory

where EventID = 'IN ROOM'

and peek(VisitID)<>VisitID

order by VisitID, SeqID



6 Replies
alexandros17
Not applicable

Re: DateTime based on lowest SeqID

Try with this:

MyTable:

LOAD
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
Comment,
DateTime;
SQL SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory
WHERE EventID = 'IN ROOM';

Left Join

Load Min(DateTime) as Mini resident MyTable Group by VisitID, SeqID;

Let me know

amarbogam
Not applicable

Re: DateTime based on lowest SeqID

See if this works,

LOAD

VisitID,SeqID,EventID,EventName,HistoryFunction,Comment,DateTime

;

SQL SELECT *

FROM livedb.dbo.EdmPatientStatusEventHistory

WHERE EventID = 'IN ROOM';

Data2:

Load

VisitID&'-'&SeqID as Key

;

LOAD

VisitID,

min(SeqID) as SeqID

resident Data

group by VisitID

;

Data3:

NoConcatenate

Load

*,

if(exists(Key,VisitID&'-'&SeqID),DateTime) as urdesireddate

resident Data;

drop table Data;

drop table Data2;

Not applicable

Re: DateTime based on lowest SeqID

Thanks, Alessandro.

After running your script with a limited load of 10,000 lines, each VisitID has thousands of values for 'Mini', rather than just one.

I'm going to update my original post to include the entire table.  Maybe that will help.

ramoncova06
Not applicable

Re: DateTime based on lowest SeqID

I believe Alessandro code just had a typo, modified so it should work no

MyTable:

LOAD

VisitID,

SeqID,

EventID,

EventName,

HistoryFunction,

Comment,

DateTime;

SQL SELECT *

FROM livedb.dbo.EdmPatientStatusEventHistory

WHERE EventID = 'IN ROOM';

Left Join

Load Min(DateTime) as DateTime,

'Yes' as Mini resident MyTable

Group by VisitID, SeqID;

maxgro
Not applicable

Re: DateTime based on lowest SeqID

EdmPatientStatusEventHistory:
LOAD

SourceID,
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
HistoryUser,
StartRW,
RowUpdateDateTime,
HistoryUserType,
Comment,
StartDateTime,
ActualFileDateTime;
SQL
SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory;


left join (EdmPatientStatusEventHistory)

load VisitID, StartDateTime as MinStartDateTime           // StartDateTime or DateTime or ....

resident EdmPatientStatusEventHistory

where EventID = 'IN ROOM'

and peek(VisitID)<>VisitID

order by VisitID, SeqID



Not applicable

Re: DateTime based on lowest SeqID

Thank you, Massimo!  There's more testing to be done, but it appears to be working well!