Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sakthi266
Contributor III
Contributor III

Show Id having Multiple Status

Hi Team, 

          Good Evening. I need to show an ID which is having multiple status. Data is attached in Excel . My result expected is given below. 

IDSupervisor IDDateStatus
O-1S-101/01/2020Active
O-1S-213/04/2020Active
O-3S-110/01/2020Active
O-3S-425/04/2020Active

 

Note : ID 2 is excluded because it is having only one supervisor with the status Active. And Also for 0-3 Supervisor 5 is having inactive status. So we excluded ID having 0-5 with Supervisor S-5. Please reply ASAP as i have to complete by tomorrow.

 

Regards, 

Sakthivel S

 

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

HI @sakthi266 

Try like below

Data:
LOAD *, ID &'-'&[Supervisor ID] as Key INLINE [
ID, Supervisor ID, Date, Status
O-1, S-1, 01/01/2020, Active
O-1, S-2, 13/04/2020, Active
O-2, S-12, 10/01/2020, Active
O-2, S-14, 12/04/2020, Active
O-2, S-12, 25/03/2020, Inactive
O-3, S-1, 10/01/2020, Active
O-3, S-4, 25/04/2020, Active
O-3, S-5, 25/04/2020, Active
O-3, S-5, 30/04/2020, Inactive
O-4, S-1, 01/01/2020, Active
O-4, S-2, 05/01/2020, Active
O-4, S-1, 10/01/2020, Cancelled
];

join
Load ID , Key , 1 as Flag Where not WildMatch(Status, '*Inactive*', '*Cancelled*');
Load ID, Key, Concat(Status, ',') as Status Resident Data group by Key, ID;

Thanks & Regards,
Mayil Vahanan R

View solution in original post

4 Replies
MayilVahanan

Hi @sakthi266 

Try like below

Data:
LOAD *, ID &'-'&[Supervisor ID] as Key INLINE [
ID, Supervisor ID, Date, Status
O-1, S-1, 01/01/2020, Active
O-1, S-2, 13/04/2020, Active
O-2, S-12, 10/01/2020, Active
O-2, S-14, 12/04/2020, Active
O-2, S-12, 25/03/2020, Inactive
O-3, S-1, 10/01/2020, Active
O-3, S-4, 25/04/2020, Active
O-3, S-5, 25/04/2020, Active
O-3, S-5, 30/04/2020, Inactive
];

Join
Load ID, Key, 1 as Flag Where not index(Status, 'Inactive');
Load ID, Key, Concat(Status, ',') as Status Resident Data group by Key, ID;

 

Front end:

Dim: ID, Supervisor ID, Date

Exp: Only({<ID={"=Sum(Flag)>=2"}, Flag={1}>}Status)

Screenshot:

MayilVahanan_0-1608025643654.png

Hope it helps

 

Thanks & Regards,
Mayil Vahanan R
sakthi266
Contributor III
Contributor III
Author

Hi Mayil Vahanan, 

                    Thanks for the reply i have one more doubt in this if there is only active and inactive the script is fine. But i forgot to mention if anything in between and inactive is not there then that also have to show which is example below

 Data:
LOAD *, ID &'-'&[Supervisor ID] as Key INLINE [
ID, Supervisor ID, Date, Status
O-1, S-1, 01/01/2020, Active
O-1, S-2, 13/04/2020, Active
O-2, S-12, 10/01/2020, Active
O-2, S-14, 12/04/2020, Active
O-2, S-12, 25/03/2020, Inactive
O-3, S-1, 10/01/2020, Active
O-3, S-4, 25/04/2020, Active
O-3, S-5, 25/04/2020, Active
O-3, S-5, 30/04/2020, Inactive
O-4, S-1, 01/01/2020, Active
O-4, S-2, 05/01/2020, Active
O-4, S-1, 10/01/2020, Cancelled
];

In the above mentioned script  O-4 is having two supervisor Active and there is no inactive but is cancelled. So this should not show which means we have to show only Status which is last active and that's too with two Supervisors.  Please let me know if you want more details

 

Regards, 

Sakthivel.S

 

MayilVahanan

HI @sakthi266 

Try like below

Data:
LOAD *, ID &'-'&[Supervisor ID] as Key INLINE [
ID, Supervisor ID, Date, Status
O-1, S-1, 01/01/2020, Active
O-1, S-2, 13/04/2020, Active
O-2, S-12, 10/01/2020, Active
O-2, S-14, 12/04/2020, Active
O-2, S-12, 25/03/2020, Inactive
O-3, S-1, 10/01/2020, Active
O-3, S-4, 25/04/2020, Active
O-3, S-5, 25/04/2020, Active
O-3, S-5, 30/04/2020, Inactive
O-4, S-1, 01/01/2020, Active
O-4, S-2, 05/01/2020, Active
O-4, S-1, 10/01/2020, Cancelled
];

join
Load ID , Key , 1 as Flag Where not WildMatch(Status, '*Inactive*', '*Cancelled*');
Load ID, Key, Concat(Status, ',') as Status Resident Data group by Key, ID;

Thanks & Regards,
Mayil Vahanan R

View solution in original post

sakthi266
Contributor III
Contributor III
Author

Hi Mayilvahanan, 

                Thank you so much. This is what i expected. Working fine.

Regards,

Sakthivel.s