Exclude a set of data if a specific record exists within the data

[#1]

I have the following set of data where an object number will have many different staus

objectno	FormattedNumber1	projectstatus
PR00006588	02.000409.01.01	E0001
PR00006588	02.000409.01.01	I0001
PR00006588	02.000409.01.01	I0002
PR00006588	02.000409.01.01	I0045
PR00006588	02.000409.01.01	I0046
PR00006588	02.000409.01.01	I0118

Where the project status = I0046 I want to exclude all of the records applicable to the object number,if I0046 doesn't exist then I would want to display the records

Can anyone help

[anbu1984]

T1:
LOAD * Inline [
objectno,FormattedNumber1,projectstatus
PR00006588,02.000409.01.01,E0001
PR00006588,02.000409.01.01,I0001
PR00006588,02.000409.01.01,I0002
PR00006588,02.000409.01.01,I0045
PR00006588,02.000409.01.01,I0046
PR00006588,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0118 ];

Inner Join(T1)
Load * Where IsNull(Max);
Load objectno,MaxString(If(projectstatus='I0046',projectstatus)) As Max Resident T1 Group By objectno;

[#3]

Thanks

The data is coming from a QVD file and therfore all my data will be loaded for multiple object numbers, what I need is the code to exclulde object numbers where the project status = I0046.

[anbu1984]

If you run the above script, Objectno PR00006588 is excluded since it has project status = I0046 and PR00006589 is retained

[#5]

Thanks...I've managed to exclude the I0046 records but I only want to see one line for the rest of my data. How can I elminate the duplicate lines

Company Code	Plant	Projectno.	WBS Element	WBS Desc.	Market	Project Manager	Milestone	Milestone Desc.	projectstatus	Actual Date	Scheduled Date
US05	USP2	02.000409	02.000409.90.00	BASIN ELECTRIC Warranty  Phase	251	Hamilton, Phil         CO	13450	Start of System Warranty	E0001	2012-07-31	2012-07-31
US05	USP2	02.000409	02.000409.90.00	BASIN ELECTRIC Warranty  Phase	251	Hamilton, Phil         CO	13450	Start of System Warranty	I0001	2012-07-31	2012-07-31
US05	USP2	02.000409	02.000409.90.00	BASIN ELECTRIC Warranty  Phase	251	Hamilton, Phil         CO	13450	Start of System Warranty	I0002	2012-07-31	2012-07-31
US05	USP2	02.000409	02.000409.90.00	BASIN ELECTRIC Warranty  Phase	251	Hamilton, Phil         CO	13450	Start of System Warranty	I0028	2012-07-31	2012-07-31
US05	USP2	02.000409	02.000409.90.00	BASIN ELECTRIC Warranty  Phase	251	Hamilton, Phil         CO	13450	Start of System Warranty	I0118	2012-07-31	2012-07-31

[anbu1984]

Which row you want to retain? Any row is fine out of duplicates?

[#7]

Yes... after removing the status of I0046 I just want to see one project status record, but I can't do it based on the maining codes as there could be many different codes in the data. I just need to aggregate the data down to one line

[#8]

Another way with not exists

Chris

[anbu1984]

T1:
LOAD * Inline [
objectno,FormattedNumber1,projectstatus
PR00006588,02.000409.01.01,E0001
PR00006588,02.000409.01.01,I0001
PR00006588,02.000409.01.01,I0002
PR00006588,02.000409.01.01,I0045
PR00006588,02.000409.01.01,I0046
PR00006588,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0002 ];

Inner Join(T1)
Load * Where IsNull(Max);
Load objectno,MaxString(If(projectstatus='I0046',projectstatus)) As Max,MinString(projectstatus) As projectstatus Resident T1 Group By objectno;

[#10]

Hi

Still having an issue with this code. The data is loading ok but where the status = I0046 it is not excluding it from the data but returning NULL. Where I0046 is not present then it seems to be working and only returning one record.