Retrieve only interlinked rows.

[dev7777]

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?

[TimvB]

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]

TemNames:

Load Name as tmpName

From Source 

Where [R-flag] = 'Y';

Load *

From Source

Where exists (tmpName, Name);

Drop table TempNames;