If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Filter data on one Data item with multiple values

Started by therese1, 06 Sep 2018 04:08:03 AM

Previous topic - Next topic

therese1

Hi
I have a report that has a booking number, I am comparing where customer codes do not match. Issue is I have one column where there could be 30 customer codes I am comparing against another column, I am using data item to say match or no.

I would like to only show booking numbers where all of the data items say NO ... eg. Booking number duplicated in 10 rows only one row has match all the others have no so I do not want to see this booking number.... if all 10 lines had no then I want to see this booking number.  I tried to write a case statement but not sure how?

sdf

Can you attach a representation of what you are trying to achieve?
That way, we can visualize what you currently have.


therese1


cognostechie

You posted your company's data online ?? You should provide only a few rows to explain the situation and post sample data.

1> First create a query with only one column 'Booking Number' and apply a filter to the query - [Data Item1] <> 'NO'. 
2> Create 2nd query with only one column 'Booking Number'.
3> Create 3rd query by using 'except' so that the result set is all Booking Numbers except the ones you want excluded.
4> Create 4th query with all columns
5> Join 4th query with 3rd on Booking Number and produce 5th query. 

The 5th query will give you only those Booking Numbers where all rows are 'NO'.

therese1

thank you ... that makes so much sense

this is dummy data not real