How to remove records per ID

[PresidReporting]

Hi,

I am trying to remove certain records from my result set based on a certain criteria, but due to how the data is it is becoming difficult.

Example:

Opportunity#	ManufacturerID
2558	-1
2558	588
230	-1

In the example above, I would like to remove the record with opportunity# 2558 and ManufacturerID of -1.  Simply telling Qlikview to remove any ManufacturerID with -1 won't work because there are Opportunities that have only one record (with a manufacturerID of -1) and we want to keep those.

Basically, any Opportunity that returns more than one record of ManufacturerIDs I would like to remove the -1 records, however if there is only one record for the Opportunity keep that one record.

In addition, this needs to be done via the loadscript because I am storing the data into a .txt file for SQL to grab.  

Thoughts?

[Saravanan_Desingh · Accepted solution]

Solution in the Script:

tab1:
LOAD * INLINE [
    Opportunity#, ManufacturerID
    2558, -1
    2558, 588
    230, -1
    231, 213
    231, 211
];

Left Join(tab1)
LOAD Opportunity#, Count(Opportunity#) As Cnt
Resident tab1
Group By Opportunity#
;

tab2:
NoConcatenate
LOAD *
Resident tab1
Where Cnt=1 OR (Cnt>1 And ManufacturerID<>-1)
;
Drop Table tab1;

[Saravanan_Desingh]

[Vegar]

This might help you.

Load *,

Opportunity# as _opp

From Data 

Where ManufacturerID <> -1;

Concatenate load *

From Data

Where 

Not Exists(Opportunity# , _opp)

And ManufacturerID =-1 ; //not necessary 

Drop field _opp;