topic Re: DateTime based on lowest SeqID in QlikView

DateTime based on lowest SeqID

Tue, 14 Apr 2015 14:48:22 GMT

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

Re: DateTime based on lowest SeqID

alexandros17 — Tue, 14 Apr 2015 15:00:07 GMT

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

Re: DateTime based on lowest SeqID

Anonymous — Tue, 14 Apr 2015 15:07:25 GMT

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;

Re: DateTime based on lowest SeqID

Tue, 14 Apr 2015 16:40:45 GMT

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.

Re: DateTime based on lowest SeqID

ramoncova06 — Tue, 14 Apr 2015 16:49:27 GMT

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;

Re: DateTime based on lowest SeqID

maxgro — Tue, 14 Apr 2015 18:01:16 GMT

EdmPatientStatusEventHistory:
LOAD

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

Re: DateTime based on lowest SeqID

Tue, 14 Apr 2015 18:47:48 GMT

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