How to implement the following logic in Qlik Data Editor?

[omkar_2611]

How to filter the rows from the below table using Where clause in Load?

Data:

ID	month	Cost	Value
1	01.01.2022	72300	2056
1	01.02.2022	72300	2056
1	01.03.2022	72300	2056
2	01.03.2022	72300	2117.68
1	01.03.2022	72500	3084
2	01.03.2022	72500	3176.52
1	01.04.2022	72500	3084
2	01.04.2022	72500	3176.52

I want to keep, all the rows with ID = 2  and the rows with ID = 1 only if there are no other rows with same month and Cost.

 The output table would look like,

ID	month	Cost	Value
1	01.01.2022	72300	2056
1	01.02.2022	72300	2056
2	01.03.2022	72300	2117.68
2	01.03.2022	72500	3176.52
2	01.04.2022	72500	3176.52

I tried the following query, but it select only ID = 2 rows.

Load * Resident Data
Where (ID = 2 or not exists(ID = 2 and month = month and Cost = Cost));

[vinieme12]

As below 

Main:

Load ID,month,cost,value 

From xyzsource; 

Inner Join(Main) 

Load month,max(ID) as ID

Resident Main 

Group By month;

[omkar_2611]

I received the following error. Also, I think, Inner Join would require a much more processing time than the where clause.

[vinieme12 · Accepted solution]

You don't need the Value field during Inner join , this is simply reducing the data is Main table 

Inner Join(Main)

Load month,max(ID) as ID

Resident Main 

Group By month;