Compare attributes for 2 columns that have one to many relationship

[Stephens]

Hello,

I am trying to compare the statuses of 2 columns connected by a one to many relationship. I have a Global_ID column that has its own status (either Active or Inactive) and each Global_ID can have multiple Plants connected to it and each plant can have its own status (also either Active or Inactive). 

For the Gloabl_ID to have a status of 'Active', at least 1 of its connected Plants must also have the status of Active. The issue I am trying to fix is one where the Global_ID status is 'Active', but none of its Plants' statuses are 'Active'. In this situation the Global_ID status should be changed to 'Inactive' to reflect the fact that all of its Plants have a status of 'Inactive'.  But I am having a hard time isolating this subset of data.

An example of the record I am trying to isolate:

Global_ID	Global_ID Status	Plant	Plant Status
G123	Active	1	Inactive
G123	Active	2	Inactive
G123	Active	3	Inactive
G123	Active	4	Inactive
G123	Active	5	Inactive
G123	Active	6	Inactive
G123	Active	7	Inactive
G123	Active	8	Inactive

In the example above, I would change the status of G123 to 'Inactive'. Any ideas on how to codify this logic and pull just the Global_IDs that need updating? 

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD * INLINE [
    Global_ID, Global_ID Status, Plant, Plant Status
    G123, Active, 1, Inactive
    G123, Active, 2, Inactive
    G123, Active, 3, Inactive
    G123, Active, 4, Inactive
    G123, Active, 5, Inactive
    G123, Active, 6, Inactive
    G123, Active, 7, Inactive
    G123, Active, 8, Inactive
    G124, Active, 1, Inactive
    G124, Active, 2, Active
    G124, Active, 3, Inactive
    G125, Inactive, 1, Active
    G125, Inactive, 2, Inactive
    G125, Inactive, 3, Inactive
];

Left Join(tab1)
LOAD Global_ID, If(Index(Concat(DISTINCT [Plant Status]),'Active'),'Active','Inactive') As New_Global_ID_Status
Resident tab1
Group By Global_ID;

[Saravanan_Desingh]

Output: