The department before info

[Qliksense_77]

Hi,

I have data like below with stays at hospital.

In one hospital stay the patients can have multiple stays in departments in the hospital. I have one row per stay at a department. 

If a patient is transfered to a new department I want to know the department the patient came from. Like the green text below.

Is this possible?

Id_hospital_stay	Date for arrival	Time for arrival	Department for stay	Date for departure	Time for departure	New patient	Desired data (department before)
21596500	10/06/2021	13:14	department A	10/06/2021	20:22	yes
21596500	10/06/2021	20:22	department B	11/06/2021	11:38	no	department A
21928720	30/07/2021	06:25	department A	30/07/2021	07:00	yes
21928720	30/07/2021	07:00	department B	31/07/2021	04:15	no	department A
21928720	31/07/2021	04:15	department C	01/08/2021	23:50	no	department B
21928720	01/08/2021	23:50	department D	02/08/2021	13:22	no	department C
21928720	02/08/2021	13:22	department E	09/08/2021	12:31	no	department D

[anat · Accepted solution]

test:
load *,if([New patient]='yes','',previous([Department for stay])) as nd;
load * inline [
Id_hospital_stay Date for arrival Time for arrival Department for stay Date for departure Time for departure New patient
21596500 10/06/2021 13:14 department A 10/06/2021 20:22 yes
21596500 10/06/2021 20:22 department B 11/06/2021 11:38 no
21928720 30/07/2021 06:25 department A 30/07/2021 07:00 yes
21928720 30/07/2021 07:00 department B 31/07/2021 04:15 no
21928720 31/07/2021 04:15 department C 01/08/2021 23:50 no
21928720 01/08/2021 23:50 department D 02/08/2021 13:22 no
21928720 02/08/2021 13:22 department E 09/08/2021 12:31 no

](delimiter is ' ');

exit script;

[Qliksense_77]

Thanks @anat ,

your solution works in this small sample.

But when I use it in the big data model with millions of rows, then it is not working.

It fetches "department of stay"  from different "Id_hospital_stay". 

You know a solution to this?

[Qliksense_77]

I combined with the "Order by" function in script and got it to work 🙂