Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephens
Contributor II
Contributor II

Compare attributes for 2 columns that have one to many relationship

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_IDGlobal_ID StatusPlantPlant Status
G123Active1Inactive
G123Active2Inactive
G123Active3Inactive
G123Active4Inactive
G123Active5Inactive
G123Active6Inactive
G123Active7Inactive
G123Active8Inactive

 

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? 

Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

2 Replies
Saravanan_Desingh

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:

commQV63.PNG