expression help

[Krish2459_58]

Hi,

I have data as below. Here I have to exclude dates where review = Market Review.

But in the below example 24000445 shared both Initial Rent and  Market Review which I have to exclude 24000445.

lease	review	date
24000445	Initial Rent	10/12/2018
24000445	Market Review	10/12/2018
28939933	Initial Rent	09/04/2027
26907933	Market Review	05/10/2029

Required output:

lease	review	date
28939933	Initial Rent	09/04/2027

[Clement15 · Accepted solution]

Hello,

this should work

Data:
Load
*,
Wildmatch(Window(Concat(review),lease),'*Market Review*') as Del
Inline [
lease ,review, date
24000445, Initial Rent, 10/12/2018
24000445, Market Review, 10/12/2018
28939933, Initial Rent, 09/04/2027
26907933, Market Review, 05/10/2029]
;

NoConcatenate

Final:
Load
lease,
review,
date
Resident Data
where Del<>1;

drop table Data;

[Qrishna · Accepted solution]

Data:
LOAD lease,
review,
date
FROM
[https://community.qlik.com/t5/App-Development/expression-help/td-p/2504578]
(html, codepage is 1252, embedded labels, table is @1);

left join(Data)
Load lease,
count(distinct review) as review_cnt
Resident Data
Group By lease;

NoConcatenate
Output:
Load *
Resident Data
Where review = 'Initial Rent' and review_cnt = 1;
Drop Table Data;