topic Re: need help with previous () in QlikView

need help with previous ()

Thu, 11 Jun 2015 09:56:59 GMT

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.

Re: need help with previous ()

Gysbert_Wassenaar — Fri, 12 Jun 2015 12:43:19 GMT

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

;

Re: need help with previous ()

kuba_michalik — Fri, 12 Jun 2015 13:26:41 GMT

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

Re: need help with previous ()

Sun, 14 Jun 2015 06:47:00 GMT

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.

Re: need help with previous ()

Sun, 14 Jun 2015 06:49:37 GMT

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.

Re: need help with previous ()

Wed, 17 Jun 2015 19:37:58 GMT

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 ??

Re: need help with previous ()

kuba_michalik — Thu, 18 Jun 2015 12:29:40 GMT

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).