Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
TemNames:
Load Name as tmpName
From Source
Where [R-flag] = 'Y';
Load *
From Source
Where exists (tmpName, Name);
Drop table TempNames;