Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: Check data for info

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Qliksense_77

Creator

2022-03-01
08:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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 |

148 Views

1 Solution

Accepted Solutions

justISO

Specialist

2022-03-08
09:05 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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;

3 Replies

Qliksense_77

Creator

2022-03-08
07:18 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

2022-03-08
09:05 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

2022-03-09
04:50 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content