Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
dev7777
Contributor II
Contributor II

Retrieve only interlinked rows.

Hello,

I am trying to create a condition where I retrieve the rows based on a flag, but want the associated ID's as well

 

RowID            PowerID         Name              R-Flag  

1                             001                     Zach                 N

2                             001                      Zach                 N

3                             002                     Bob                  N

4                             002                      Bob                 Y

5                             003                     kathy               N

6                             004                      Kathy               N

 

 

Out of these records, I only want to show records of Bob who has R-flag as Y. But want both records (row no) 3 & 4. Is there a way to meet this requirement in Qlik?

 

Labels (2)
2 Replies
TimvB
Creator II
Creator II

At first we need to find who (Name) has at least one 'Y' in the field “R-Flag”:

 

NamesFlagged_map:

Mapping Load Distinct

Name,

'1' as NameFlag

Resident YourTable

Where R_Flag = 'Y';

 

Second, we need to flag the name records in the original table that have at least one 'Y'.

 

NamesFlagged_tmp:

NoConcatenate Load *,

ApplyMap('NamesFlagged_map', Name, '0') as NameFlag

Resident YourTable;

Drop Table YourTable;

 

Third, filter the names of the original table that are flagged:

 

NamesFlagged:

NoConcatenate Load *

Resident NamesFlagged_tmp
Where NameFlag = '1';

Drop Table NamesFlagged_tmp;

Drop Field NameFlag From NamesFlagged;

 

The NamesFlagged table will contain only the records of the persons that had at least one R-Flag marked as 'Y'. 

Hope it helps!

Vegar
MVP
MVP

TemNames:

Load Name as tmpName

From Source 

Where [R-flag] = 'Y';

 

Load *

From Source

Where exists (tmpName, Name);

Drop table TempNames;