Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
Qliksense_77
Creator
Creator

Check data for info

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
Labels (1)
1 Solution

Accepted Solutions
justISO
Specialist
Specialist

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;

View solution in original post

3 Replies
Qliksense_77
Creator
Creator
Author

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

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
Creator
Creator
Author

Hi @justISO 

It works perfect,

thank you 😊