Show Id having Multiple Status

[sakthi266]

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. 

ID	Supervisor ID	Date	Status
O-1	S-1	01/01/2020	Active
O-1	S-2	13/04/2020	Active
O-3	S-1	10/01/2020	Active
O-3	S-4	25/04/2020	Active

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

[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:

Hope it helps

[sakthi266]

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 · Accepted solution]

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;

[sakthi266]

Hi Mayilvahanan, 

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

Regards,

Sakthivel.s