Check data for info

[Qliksense_77]

Hi, 

I have data from a hospital. My data have a row per stay at a department in the hospital with the name of the department and an ID for the hospital-stay. The same patient can have stays in multiple departments during the same hospital-stay. 

I need to do a check of data to get information from all the patients different stays  at departments during their hospital-stay.

I want to know if patients only have a stay at only one specific department. Or if the patients only have  stays at two specific departments. And finally if the patients have stays at two specific departments and at least one stay in 3. department. 

I have tried to illustrate it  below. The red text is the info that I am searching for. I would like to find a solution for use in load script.

Anybody that can help? 

Id	Department	Wanted info:
1	A	Only A
2	A	Only A and B
2	B	Only A and B
3	A	A, B and other department
3	B	A, B and other department
3	C	A, B and other department
3	D	A, B and other department
4	E	Other

[Qliksense_77]

Anybody that can help here ? 

I have tried to use the p and e function in set analysis. But I cant get that to work. And I prefer a solution in script editor.

[justISO · Accepted solution]

Hi, if you not have dozens of departments here you can try something like this maybe:

main:
Load * inline
[Id, Department
1,A,
2,A,
2,B,
3,A,
3,B,
3,C,
3,D,
4,E ];

temp:
Load distinct Id,
count(if(Department='A',1)) as A,
count(if(Department='B',1)) as B,
count(if(Department='C',1)) as C,
count(if(Department='D',1)) as D,
count(if(Department='E',1)) as E
Resident main
group by Id;

left join (main)
Load
Id,
if( A>0 and B=0 and C=0 and D=0 and E=0, 'Only A',
if( A=0 and B>0 and C=0 and D=0 and E=0, 'Only B',
if( A>0 and B>0 and C=0 and D=0 and E=0, 'Only A and B',
if( A>0 and B>0 and (C>0 or D>0 or E>0), 'A, B and other',
if( A=0 and B=0 and (C>0 or D>0 or E>0), 'Other', 'CHECK'))))) as wanted_info
resident temp;

drop table temp;

[Qliksense_77]

Hi @justISO 

It works perfect,

thank you 😊