need help with previous ()

[#1]

Hi

A friend set me an exercise and i think im on the right track but i have gotten stuck.

The scenario is looking at patient admissions and readmission. Im stuck when it comes to looking at readmission. I dont seem to get previous correct, at least not the whole way. I wasnt to be able to choose a patient and then see if that patient is readmitted (and when).

VårdID = specific ID for each hospitalisation

PatientID = specific ID for each patient

Start = time of admission (timestamp)

Slut = time of discharge (timestamp)

Occupancy = how many days a patient has been at the hospital for.

ps, DateID is then used in a master calendar. I can post that part as well if needed.

Thank you very much

TempVårdTid:

LOAD

     VårdID,

     PatientID,

     Start,

     Slut  

FROM

[nico återinläggning.xlsx]

(ooxml, embedded labels, table is Blad1);

NoConcatenate

VårdTid:

LOAD

    VårdID,

    Start,

    Slut,

    Occupancy,

    PatientID,

    TempDateID + Iterno() - 1 as DateID

    While TempDateID + IterNo() - 1 <= EndDateID;

LOAD

     VårdID,

     VårdID AS Occupancy,

     PatientID,

     Start,

     Floor (Start) as TempDateID,

     if(IsNull(Slut), Floor(Today()), Floor(Slut)) as EndDateID,

     Slut  

Resident TempVårdTid;

TempAdmission:

noConcatenate

LOAD

     VårdID,

     VårdID AS Admission,

     PatientID,

     Start,

     Slut,

     Floor(Start) as DateID

//     previous (VårdID) as Next

Resident TempVårdTid;

//order by PatientID desc;

Admission:

Concatenate (VårdTid)

LOAD

     VårdID,

     Admission,

     PatientID,

     Start,

     Slut,

     DateID,

     previous (Admission) as Next

Resident TempAdmission

order by PatientID, DateID  desc;

drop table TempAdmission;

Discharge:

Concatenate (VårdTid)

LOAD

     VårdID,

     VårdID AS Discharge,

     PatientID,

     Start,

     Slut,

     Floor(Slut) as DateID

Resident TempVårdTid

Where not IsNull(Slut);

drop table TempVårdTid;

Message was edited by: nicola cavallini Attached a file I have been practicing with, hopefully making what i need more clear.

[Gysbert_Wassenaar]

I think that you misunderstand what previous does. Previous merely looks at the previous record of the source table. It can't look into any other tables. It also won't check if other fields from the previous record are different or the same as the current record. Those check are up to you.

You need to create a table with all the admissions sorted by patient and admission date. Then you can use the previous function:

LOAD

     PatientID,

     AdmissionDate,

     If(Previous(PatientID)=PatientID, 1, 0) as IsReadmission

     ...other fields...

RESIDENT ...some_table....

ORDER BY PatientID, AdmissionDate

;

[kuba_michalik · Accepted solution]

You are missing some way of checking if the Previous(Admission) is actually related to the same patient.

Something like:

If(Previous(PatientID)=Patient, Previous(Admission)) as Next

instead of

previous (Admission) as Next

[#4]

Thank you very much. I try to look at another table, or at least not purposely. The issue is that AdmissionsTImes (Start) arent unique, thats why i tried to use "vårdID".  Did you have an opportunity to look at the excel file I attached? Isnt that table sorted like you explain?

I will look at your example in dept this evening when the kids are sleeping and dont want to serve me imaginary coffe 😃

Sorry for taking this long to reply. I have been stomach ill for the last 3 days.

[#5]

Thank you very much for taking the time to help me.

I thought that I missed something like that but all my attempts to create something just caused havoc. I will try this evening when said kids are sleeping.

Thank you much very both of you. I really appreciate it.

[#6]

Thank you Jakub, that answered my question perfectly. I only had to make sure to sort by PatientID and Start.

Unfortunately that was only part of what I needed. Now I need to figure out a few more things, e.g. how to calculate how far (in days) in the future ReAdission (Next) is located takes place.

Any suggestion ??

[kuba_michalik]

Floor(Next) - Floor(Start)

should give you a difference in days between two dates. Just make sure to use it after the [Next] field is actually created (using preceding load or in a subsequent load resident).