How do I exclude an entire set based on an attribute of one member?

[#1]

The title is probably confusing, but I'm trying to exclude a series of rows based on content of any one of them.

In SQL this would be something like:

SELECT COL1 FROM TBL

WHERE COL1 NOT EXISTS (SELECT COL1 FROM TBL WHERE COL2 = 'X');

In data form:

Col1    Col2

1

1

1

2

2         X

2

3

3

3

I want to exclude all rows where Col1 = 2 because one of them has a an 'X' in COL2.

In QVW, the data is coming from some CSVs so I can't do any preprocessing. Any hint to what the LOAD RESIDENT statement would look like to do a post-load analysis, or should I be doing something with a calculated dimension?

Thanks

[rustyfishbones]

Check this video it will help

https://www.youtube.com/watch?v=fEVHD12MsPo

[#3]

Thanks but that doesn't do what I need.

In his example I would go from....

Col1    Col2

1

1

1

2

2         X

2

3

3

3

to

Col1    Col2

1

1

1

2

2

3

3

3

whereas I need to get to

Col1    Col2

1

1

1

3

3

3

[prodanov · Accepted solution]

Try this:

tmp:

Mapping LOAD Distinct [Col1],

     1

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet2)

where Col2='X' ;

tbl:

load [Col1],

     Col2

FROM

[..\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet2)

where ApplyMap('tmp',[Col1   ],0)=0

[#5]

Perfect. Thanks a lot.